Need to identify names with atleast one special character in TD.
For ex: 'Robert John'
'Rama / Ram'
Out of these need to select below names, i.e. with atleast one spec character.
'Rama / Ram'
Please suggest an apporach. I tried to travarse through the string checking each character using WITH RECURSIVE, but could not get the result.
Chandrashekar k S
First you need to define 'special characters' and then you need to use them in LIKE statement.
SELECT * FROM Table1 WHERE Col1 LIKE ANY ('%/%', '%.%');
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
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', '')
I have a situation where I need to eliminate the special characters from the ip address.
For Example, I have the data stored as .22.214.171.124] 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!
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.
if you need to split the ip into octets STRTOK might be what you need, e.g.
STRTOK('.126.96.36.199]', ' .]', 3)
returns the 3rd octet, '89'