All Forums Database
rtakle_d 9 posts Joined 09/13
05 Sep 2013
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

rtakle_d 9 posts Joined 09/13
05 Sep 2013

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 9 posts Joined 09/13
09 Sep 2013

Hi Team ,
Do you have any solution and suggestion for above request .
Regards,

dnoeth 4628 posts Joined 11/04
11 Sep 2013

 
If you know which characters are not special it's easy:
CHAR_LENGTH(oTranslate(your_column, 'abcdefghijklmnopqrstuvwxyz','') > 0 indicates special characters.
 
Dieter

Dieter

rtakle_d 9 posts Joined 09/13
12 Sep 2013

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.

M.Saeed Khurram 544 posts Joined 09/12
12 Sep 2013

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

rtakle_d 9 posts Joined 09/13
12 Sep 2013

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 4628 posts Joined 11/04
12 Sep 2013

@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

Dieter

rtakle_d 9 posts Joined 09/13
12 Sep 2013

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 4628 posts Joined 11/04
12 Sep 2013

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

Dieter

rtakle_d 9 posts Joined 09/13
23 Sep 2013

@Dieter:
Thanks a lot dieter.After installing that UDF function above solution is working.Really thanks a lot for your help.
Regards,
Rahul

Glass 225 posts Joined 04/10
02 Oct 2013

I'm not so fortunate.
I installed the UDF, tested it using the samples in the btq file and it works fine,
but when I try to use it to detect invalid characters using:
where CHAR_LENGTH(oTranslate(your_column, 'abcdefghijklmnopqrstuvwxyz','') > 0
The columns in my table that I've inserted invalid characters (%*~^) for instance are not returned.
Any Idea what I may be missing?
13.10.06
 
 

dnoeth 4628 posts Joined 11/04
02 Oct 2013

oTRANSLATE is case sensitive, you need to either add chars 'A' to 'Z' or use 

OTRANSLATE(UPPER(your_column), 'ABCDEFGHIJKLMNOPQRSTUVWXYZ','')

 
Dieter

Dieter

tgorner 4 posts Joined 10/08
03 Oct 2013

It seems like there has to be something between ''. At least for me, it doesn't work if it's just an empty string. Wonder why...
This gives :  566

SELECT OTRANSLATE('abcd566', 'abcdefghijklmnopqrstuvwxyz',' ') (VARCHAR(10) )

 
This on the other hand gives null:

SELECT OTRANSLATE('abcd566', 'abcdefghijklmnopqrstuvwxyz','') (VARCHAR(10) )

 

dnoeth 4628 posts Joined 11/04
03 Oct 2013

Hi Thomas,
your not using the built-in TD14 oTranslate function :-)
It must be a custom implementation which exactly mimics Oracle's empty Varchar equals NULL "feature".
The Oracle way to avoid this is adding a dummy character like:

SELECT OTRANSLATE('abcd566', '.abcdefghijklmnopqrstuvwxyz','.') (VARCHAR(10) )

 
Dieter

Dieter

tgorner 4 posts Joined 10/08
03 Oct 2013

Thanks Dieter. Now I recall, that you might have told me that already in the past... Guess I have either a déjà vu, or a memory leak :-)

dbsar 1 post Joined 12/14
10 Dec 2014

Hi,
I am using otranslate as below syntax and it gives me error as 6709: The string contains untranstable character.
otranslate(Coalesce(Contractor_name,''),':',' ') new_name
Can anyone help?
Thank you!

mnagara 6 posts Joined 11/14
14 Dec 2014

Otranslate is an existing function in teradata. Usually DBA uses special schema for all UD functions like 'CRM_UD_FUNCTION.OTRANSLATE()'. Please confirm with DBA for schema & it's access. Function will surely be implemented by DBA.

Thanks,
Mikhil

You must sign in to leave a comment.