Q

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.

This Content Component encountered an error

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

 

This was first published in January 2010

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close