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

[Frank de Vries]

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.

[Peter Godron]

Frank,
does this help:
http://en.wikipedia.org/wiki/Oracle_metadata#Use_of_underscore_in_table_and_column_names

http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/conditions016.htm

[Frank de Vries]

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

[Peter Godron]

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]

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]

...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

[Frank de Vries]

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