Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Non Printable Characters
- Thread starter manmaria
- Start date
- Thread starter
- #3
The only way i can think of is to use all possible chars within LIKE ANY.
I forgot the percent char in my first example, so it's quite ugly:
col LIKE ANY ('%'|| '00'xc || '%', '%'|| '01'xc || '%',...)
IIRC the percent sign is '25'xc, so this could be rewritten as:
col LIKE ANY ('250125'xc, '250125'xc,...)
A more elegant solution is to create a table with a single char column, populate it with all non-printable chars and then use a subquery within LIKE:
col LIKE ANY (select '%'|| charcol || '%' from mytable)
As long as your data isn't UNICODE, this should be efficient...
Dieter
I forgot the percent char in my first example, so it's quite ugly:
col LIKE ANY ('%'|| '00'xc || '%', '%'|| '01'xc || '%',...)
IIRC the percent sign is '25'xc, so this could be rewritten as:
col LIKE ANY ('250125'xc, '250125'xc,...)
A more elegant solution is to create a table with a single char column, populate it with all non-printable chars and then use a subquery within LIKE:
col LIKE ANY (select '%'|| charcol || '%' from mytable)
As long as your data isn't UNICODE, this should be efficient...
Dieter
- Status