Databases
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