All Forums Database
Chandrashekarks 11 posts Joined 11/10
24 Apr 2013
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

Adeel Chaudhry 773 posts Joined 04/08
24 Apr 2013

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 ('%/%', '%.%');

-- If you are stuck at something .... consider it an opportunity to think anew.

dnoeth 4628 posts Joined 11/04
24 Apr 2013

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

Dieter

Chandrashekarks 11 posts Joined 11/10
25 Apr 2013

Thanks Chaudary and Dieter.
I tried 'with recursive', it worked.  
 
Regards
Chandrashekar K S
 
 
 

rtakle_d 9 posts Joined 09/13
12 Sep 2013

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

GIRINJ 4 posts Joined 11/14
01 Dec 2014

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.

dnoeth 4628 posts Joined 11/04
01 Dec 2014

TD 14 supports regular expressions:

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

 

Dieter

GIRINJ 4 posts Joined 11/14
03 Dec 2014

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.

dnoeth 4628 posts Joined 11/04
04 Dec 2014

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'

Dieter

GIRINJ 4 posts Joined 11/14
05 Dec 2014

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

GIRINJ 4 posts Joined 11/14
23 Dec 2014

Hi Friends,
I face this error : Bad character in format or data of ipaddress.cdot
when I try to run the below code:
select
     ip_addr ,
   cast ( substr ( ip_addr , 1 , adot - 1 ) as bigint ) * 16777216 +
   cast ( substr ( ip_addr , adot + 1 , bdot - 1 - adot ) as integer ) * 65536 +
   cast ( substr ( ip_addr , bdot + 1 , cdot - 1 - bdot ) as integer ) * 256 +
   cast ( substr ( ip_addr , cdot + 1 , ddot - 1 - cdot ) as integer ) as ip_integer ,
   cast ( ip_integer / 16777216 as bigint ) as ablock ,
   cast (( ip_integer - ablock * 16777216 ) / 65536 as bigint ) as bblock
   from ipaddress5
could someone help me identify the issue?
 
Thanks,
GIRINJ

You must sign in to leave a comment.