Skip to Main Content
  • Questions

  • query with like '...._....' where _ is not a wildcard

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Lim .

Asked: May 02, 2000 - 1:21 pm UTC

Last updated: January 19, 2004 - 9:31 am UTC

Version:

Viewed 10K+ times! This question is

You Asked

I wish to query the object name start with S_ in
DBA_OBJECTS, so I issue the SELECT statement as below,
select object_name
from dba_objects
where object_name like 'S_%';

But what I get is all the objects start with S.

Can you please tell me why this happen, and to issue the SQL
statement to get what I want.

Tks.




and Tom said...



% matches 0, one or more of ANY character.
_ matches any ONE character (eg: where X like 'A_B' is the same as where X like 'A%B' and length(x) = 3)

you can code:

select * from dba_objects where object_name like 'S\_%' escape '\';

you get to pick the escape character and by placing it in front of _ or %, you can make them be 'normal' characters.


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Quick follow up

Doug, March 12, 2002 - 2:05 pm UTC

Is there anyway to specifiy alphanumeric ranges? Like [a-d]
or [a-z] to indicate an alphanumeric character?

Tom Kyte
March 12, 2002 - 4:58 pm UTC

Not with like, see
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:2200894550208 <code>
for an approach to this

escape character and nvarchar2

Andy, January 19, 2004 - 2:37 am UTC

Tom, I don't understand this behaviour with escape characters and nvarchar2 columns:

mires@linux2-l2ifag> create table y(vc varchar2(100), nvc nvarchar2(100));

Tabelle wurde angelegt.

mires@linux2-l2ifag> insert into y values ('ab/c', 'xy/z');

1 Zeile wurde erstellt.

mires@linux2-l2ifag> select count(*) from y where vc like 'ab//%' escape '/';

COUNT(*)
----------
1

mires@linux2-l2ifag> select count(*) from y where nvc like 'xy//%' escape '/';
select count(*) from y where nvc like 'xy//%' escape '/'
*
FEHLER in Zeile 1:
ORA-01425: Escape-Zeichen muss eine Zeichenfolge der Länge 1 sein

mires@linux2-l2ifag> select count(*) from y where nvc like 'xy//%' escape N'/';

COUNT(*)
----------
1

I've read in the 9i SQL reference that Oracle automatically converts my escape
character into the same data type as the column I am LIKE-ing, but why does that
mean that my escape character '/' is "lost", but N'/' isn't?



Tom Kyte
January 19, 2004 - 9:31 am UTC

not knowing your environment or anything -- makes things hard.

but -- to get a nchar characterstring constant -- you use N'constant', that is the way it works. if you want to use the nvarchar/nchar -- you'll be using n'x'.

same datatype -- wrong characterset is the problem in the above, '/' is not the same as n'/'

I think you want to stick with n' in all cases -- even on your like string.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.