Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

In oracle how to query an underscore _ as a literal underscore

Frank de Vries
Respected Contributor

In oracle how to query an underscore _ as a literal underscore

Hi forum,
As you probably know in oracle there
are two wildcards % (compared to unix *)
and _ (compared to unix ?)

Now if you have a number of tables which
contain in the name an underscore example:
B1_data
B2_data
...
Bn_data

Then my query to retrieve all the table name
would normally be:
select * from user_tables where table_name like 'B_%'

However this in Oracle retrieves all tables
starting with B and not B_ , as the _ underscore is seen a single character wildcard.

So I get not only
B1_data
B2_data
...
Bn_data
but also the tables
Books_data
Building
Besijit etc..

How to get around this ?
Can I somehow quote the underscore , so it
is seen as a litteral and not as wildcard.



Look before you leap
6 REPLIES
Peter Godron
Honored Contributor
Frank de Vries
Respected Contributor

Re: In oracle how to query an underscore _ as a literal underscore

Sorry Peter that didn't help,
the 2nd link didn't even work.

However you inspired me to have a go at google
and I found this
http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1159530743/0

tested it and it works.
So thanks
Look before you leap
Peter Godron
Honored Contributor

Re: In oracle how to query an underscore _ as a literal underscore

Frank,
sorry my answer didn't really help with your problem!

I thought you would understand link syntax of the second link.

You have to remove the
part before the .htm to get the proper page.

Solution on the page:
"SELECT last_name
FROM employees
WHERE last_name LIKE '%A\_B%' ESCAPE '\';
"
Hein van den Heuvel
Honored Contributor

Re: In oracle how to query an underscore _ as a literal underscore

Peter, I appreciated the first link because of its source. And it does very nicely point to a prossible solution if you just read carefully:

"Use of underscore in table and column names.

The underscore is a special SQL pattern match to a single character and should be escaped if you are in fact looking for an underscore character in the LIKE clause of a query.

Just add the following after a LIKE statement:

ESCAPE _
And then each literal underscore should be a double underscore: __ "

The other solution used ! as escape. Same thing.

Cheers,
Hein.
Eric Antunes
Honored Contributor

Re: In oracle how to query an underscore _ as a literal underscore

...or where substr(object_name,1,1) = 'B' and substr(object_name,2,1) = '_'

PS: see my reply to the dbua thread...

Best Regards,

Eric
Each and every day is a good day to learn.
Frank de Vries
Respected Contributor

Re: In oracle how to query an underscore _ as a literal underscore

Thanks Eric , I keep that in mind.

Hein, I did read the Escape _ from Wikipedia,
but that didn't mean anything to me at the time.

Or I was just tool lazy and sought out a lazy hands-on solution,
not a test to crunch my grey cells over,
without knowing what I was doing.

With hindsight having seen a concrete example
it makes sense :)

Thanks all the same
Look before you leap