Check for special character in a string

Database
Enthusiast

Check for special character in a string

Dear Folks,

          Need to identify names with atleast one special character in TD.

For ex:   'Robert John'

              'Rama / Ram'

             '.Akbar_raj'

            'New_york_2'

           '..###'

Out of these need to select below names, i.e. with atleast one spec character.

              'Rama / Ram'

             '.Akbar_raj'

             'New_york_2'

            '..###'

         Please suggest an apporach. I tried to travarse through the string checking each character using WITH RECURSIVE, but could not get the result.

Regards

Chandrashekar k S

10 REPLIES
Teradata Employee

Re: Check for special character in a string

First you need to define 'special characters' and then you need to use them in LIKE statement.

Example:

SELECT * FROM Table1 WHERE Col1 LIKE ANY ('%/%', '%.%');

Junior Contributor

Re: Check for special character in a string

Hi Chandrashekar,

if oTranslate is installed it's easier.

When you got a specific list of those special characters you might do

WHERE CHAR_LENGTH(col) <> CHAR_LENGTH(oTranslate(col, '/_#.', ''))

A different approach is to define the non-special characters and then:

WHERE CHAR_LENGTH(oTranslate(col, 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', '')) = 0

Dieter

Enthusiast

Re: Check for special character in a string

Thanks Chaudary and Dieter.

I tried 'with recursive', it worked.  

Regards

Chandrashekar K S

Enthusiast

Re: Check for special character in a string

hello Chandrashekar K S,

Can you please let me know that recursive funtion and how code will work with select stm.

WHERE CHAR_LENGTH(oTranslate(col, 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', '')

Rahul

Fan

Re: Check for special character in a string

Hi,

I have a situation where I need to eliminate the special characters from the ip address.

For Example, I have the data stored as .88.209.89.132]    and I need to read only the ip address and eliminate the dot and the parantheses.

Can someone help me how to handle this? Your help is much appreciated!

Thanks,

GIRINJ.

Junior Contributor

Re: Check for special character in a string

TD 14 supports regular expressions:

REGEXP_SUBSTR(col, '([0-9]{1,3}\.){3}[0-9]{1,3}')
Fan

Re: Check for special character in a string

Hi Dieter,

Thank you very much. I will try this out. But I need to eliminate only the first dot and the last parantheses. I need the remaining dots as it is because I subsequently use a instr function (locate function) to store each value of the address for further manipulations. Any suggestion would be helpful.

Thanks,

GIRINJ.

Junior Contributor

Re: Check for special character in a string

Hi GIRINJ,

if you need to split the ip into octets STRTOK might be what you need, e.g.

 STRTOK('.88.209.89.132]', ' .]', 3) 

returns the 3rd octet, '89'

Fan

Re: Check for special character in a string

 Thanks a lot!! This is exactly what I wanted !

GIRINJ