SQL for finding control chars in string data type

Database
Enthusiast

SQL for finding control chars in string data type

I am trying to find a way to detect control characters in strings. How can you actually view a control character if it exists?

5 REPLIES
Senior Apprentice

Re: SQL for finding control chars in string data type

What do you mean by "detect"?

See the actual character?

CHAR2HEXINT returns a hex representation of a string.

Using a WHERE condition to filter those rows?

I did something similar using the Oracle TRANSLATE function all removing control characters:

WHERE col <> otranslate(col, latin '090a0c'xc, '')

Or use a LIKE if it's a only a single/few character(s)

Dieter

Enthusiast

Re: SQL for finding control chars in string data type

I am doing data profiling and I need to write SQL to verify if control {non-printable characters} reside in those columns.

Enthusiast

Re: SQL for finding control chars in string data type

sel * from DATABSENAME.TABLENAME
where INDEX(COL_NAME,'00'XC) <> 0

This code will give me results for non printable hex value 0, is there is a way I can do the same for all non printable characters.

Senior Apprentice

Re: SQL for finding control chars in string data type

The easiest way is to install the oTranslate UDF, otherwise you have to OR lots of conditions or

col LIKE ANY ('%'||'00'xc||'%','%'||'09'xc||'%','%'||'0A'xc||'%','%'||'0C'xc||'%',...)

Dieter

Enthusiast

Re: SQL for finding control chars in string data type

Thanks so much.