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

    Requires Free Membership to View

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, '*?<>|', '     ')


This was first published in January 2010

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: