How to list rows with special characters in PL/SQL

Want to learn how to list rows with special characters in PL/SQL? Read this tip from our PL/SQL expert for advice on how to write a PL/SQL query using the translate, char or trunk functions.

I am trying to list the rows which contain the following special characters: *, ?, <, >, |.

I have tried this but can't seem to get it to work. Any help would be appreciated. (dname is a string)

select dataid, dname, max(to_char(auditdate,'YYYY-MM-DD')) as "Audit Date" from daudit where dname like ‘%*%’ or (dname like ‘%?%’) or (dname like ‘%<%’) or (dname like ‘%>%’),or (dname like ‘%|%’) group by dataid, dname order by dataid

Are you getting an error or just not the results you were expecting?

At a glance, your query looks OK, except perhaps for the paired single-quotes which are different from straight single-quotes (but perhaps are a relic of a word processor and not how you originally wrote your query?).

If the purpose of the to_char() function on auditdate is simply to remove the time component, consider using trunc() instead. String conversions tend to be more expensive operations than other manipulation functions.

Searching a string for one or more possible characters might be done more efficiently with regular expressions (assuming you’re on 10g or later). Or you can use the translate() function to convert the special characters to spaces, then see whether the translated and untranslated strings are different:

Where dname != translate(dname, '*?<>|', '     ')


Dig Deeper on Using Oracle PL-SQL