Special Characters in Teradata

[rtakle_d]

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 …

1. We can make the column values as NULL which contains special character.
2. If there is any character which is NULL in any of DIAG_I_X field, then all the DIAG_I_X columns for that Claim key should be nullified.
3. This is to make sure that it’s better to return NO data rather than giving garbage data.

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

[rtakle_d]

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 …

1. We can make the column values as NULL which contains special character.
2. If there is any character which is NULL in any of DIAG_I_X field, then all the DIAG_I_X columns for that Claim key should be nullified.
3. This is to make sure that it’s better to return NO data rather than giving garbage data.

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

[rtakle_d]

Hi Team ,

Do you have any solution and suggestion for above request .

Regards,

[dnoeth]

If you know which characters are not special it's easy:

CHAR_LENGTH(oTranslate(your_column, 'abcdefghijklmnopqrstuvwxyz','') > 0 indicates special characters.

Dieter

[rtakle_d]

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.

[MSaeedKhurram]

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?

[rtakle_d]

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

[dnoeth]

@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.

@Rahul:

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

[rtakle_d]

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 .

[dnoeth]

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