Special Characters in Teradata

Database
Enthusiast

Special Characters in Teradata

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

17 REPLIES
Enthusiast

Re: Special Characters in Teradata

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

Enthusiast

Re: Special Characters in Teradata

Hi Team ,

Do you have any solution and suggestion for above request .

Regards,

Senior Apprentice

Re: Special Characters in Teradata

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

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

Dieter

Enthusiast

Re: Special Characters in Teradata

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.

Enthusiast

Re: Special Characters in Teradata

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?

Khurram
Enthusiast

Re: Special Characters in Teradata

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

Senior Apprentice

Re: Special Characters in Teradata

@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

Enthusiast

Re: Special Characters in Teradata

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 .

Senior Apprentice

Re: Special Characters in Teradata

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