Hi guys!
like the title is saying, i want to pull just the alphanumeric data from a column
i tried : select * from tableX where columnY NOT LIKE '%[^a-Z0-9]%' but it's not working.
EX: COLMNx
qwni546
vdfo&345k
noi7#b)f
output should be:
COLMNx
qwni546
-----------------------
Also, is there a function in one line to replace special chars with '' ? or do i have to write REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(COLUMN,'!', '' ), '#', '' ), '$', '' ), '&', '' ....
EX: COLMNx
qwni546
vdfo&345k
noi7#b)f
output should be:
qwni546
vdfo345k
noi7bf
-----------------------
appreciate any comment !
S.
Hi.
Yo may use oTRANSLATE() to get rid of the unwanted characters.
HTH.
Cheers.
thanks CarlosAL OTRANSLATE() works fine !
OTRANSLATE(COLUMN, '+~`!@#$%^&*()[]_- ={{}}|\:;"<,>.?/','')
Note: but i think i am missing ' because i cannot put it, otherwise it will be as end of string and i cannot use ESCAPE because i am filtring on all the special chars.
Still waiting for How I filter special Chars(*&^%...) in the WHERE clause plz.
S.
Hi.
The single quote is the escape for single quotes ;-). Try '' instead of '.
If in TD 14, you could use REGEXP_REPLACE().
SELECT REGEXP_REPLACE(COLUMN, '[\W]', '', 1, 0, 'c') or any of its variations [^[0-9a-zA-Z ]]*...
HTH.
Cheers.
Carlos.
thanks Carlos,
i used otranslate to filter columns contain special chars:
sel columnY from table
WHERE LENGTH(OTRANSLATE(columnY, '+~`!@#$%^&*()[]_- ={{}}|\:;"<,>.?/','')) = LENGTH (TRIM (ColumnY)).
regards,
S