We are facing an urgent problem where we need to find out the special characters in a Teradata column. Do you know how can we find it?
The special character can be smiley/@#$%^&*/any winding font character. We don’t have a comprehensive list of special characters.
Below potion we can use …
Here is suggested algorithm –
Check Colum if it contains any special character –
If yes then Update for this Claim key with NULL
Else
Check for Colum for special character
----handle the scenario
Please help me out regarding below issue .if someone have solution please let me know
We are facing an urgent problem where we need to find out the special characters in a Teradata column. Do you know how can we find it?
The special character can be smiley/@#$%^&*/any winding font character. We don’t have a comprehensive list of special characters.
Below potion we can use …
Here is suggested algorithm –
Check Colum if it contains any special character –
If yes then Update for this Claim key with NULL
Else
Check for Colum for special character
----handle the scenario
Please help me out regarding below issue .if someone have solution please let me know
Hi Team ,
Do you have any solution and suggestion for above request .
Regards,
If you know which characters are not special it's easy:
CHAR_LENGTH(oTranslate(your_column, 'abcdefghijklmnopqrstuvwxyz','') > 0 indicates special characters.
Dieter
Thanks Dieter for update.
However i am not able to write query base on your input.
Could you please let me know how to write query base on function which you gave.
There is a function in Teradata named REGEX_SIMILAR that can detect the presence of special characters according to a regular expression.
I have tried to find help about didn't find much.
Dieter can you please comment where to get help related to these functions?
Some More update .
Actual data in table .
Colum1 Colum2
ABC Abc%^*
ABC PAG
XYX PLM
After removing special charater data should be...
Colum1 Colum2
ABC
ABC
XYX PLM
So for value ABC if we found any special charater in colum2 then all colum2 value need to update as Null
for all ABC rows.
Rahul
@Khurram:
AFAIK regular expressions in Teradata are based on the PCRE open source library:
http://en.wikipedia.org/wiki/PCRE
There are lots of books and online tutorials for regular expressions:
http://en.wikipedia.org/wiki/Regular_expression
I only know the very basic stuff, but REGEXP_SIMILAR(x, '[a-z]+', 'i') should return 0 when there's any special char.
CASE
WHEN CHAR_LENGTH(oTranslate(your_column, 'abcdefghijklmnopqrstuvwxyz','') > 0
THEN NULL
ELSE your_column
END
should return NULL when there's any char outside of a to z.
But i wonder why you remove 'PAG', this seem sto be a valid string?
Dieter
Thanks dnoeth.
our project requirement is like that if any corresponding value related to “ABC” is exist with special character than we have to update all with null.
I am not able to use "oTranslate" function in my sql assistance .i have check in one of your post that we need to install this function with the help of Teradata DBA.
After installing this all other function will work fine . I mean to say that there will not be any side effect due to this .
Of course there's no side effect when you install oTranslate if it didn't exist before.
And the update is basic:
UPDATE tab
SET column2 = NULL
WHERE
column1 IN
(SELECT column1 FROM tab
WHERE CHAR_LENGTH(oTranslate(your_column, 'abcdefghijklmnopqrstuvwxyz','') > 0)
Dieter