How I filter special Chars(*&^%...) in the WHERE clause, i want to pick just Alphanumeric.

[Shoushounette]

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.

[CarlosAL]

Hi.

Yo may use oTRANSLATE() to get rid of the unwanted characters.

HTH.

Cheers.

[Shoushounette]

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.

[CarlosAL]

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.

[Shoushounette]

thanks Carlos,

i used otranslate to filter columns contain special chars:

sel columnY from table

WHERE  LENGTH(OTRANSLATE(columnY, '+~`!@#$%^&*()[]_- ={{}}|\:;"<,>.?/',''))  = LENGTH (TRIM (ColumnY)).

regards,

S