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
Related Q&A from Dan Clamage
A reader asks Oracle expert Dan Clamage about a single date condition mucking up a dynamic query. Continue Reading
One reader asks expert Daniel Clamage about the PL/SQL to_date and to_char functions and how to properly convert date and string values. Continue Reading
Oracle PL-SQL expert Daniel Clamage answers a question about a problem with doing an “insert” in PL-SQL to create a table dynamically. Continue Reading