topic Re: In oracle how to query an underscore _ as a literal underscore in Operating System - HP-UX

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

Frank de Vries — Thu, 09 Nov 2006 05:37:13 GMT

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.

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

Peter Godron — Thu, 09 Nov 2006 05:43:16 GMT

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

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

Frank de Vries — Thu, 09 Nov 2006 07:27:58 GMT

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

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

Peter Godron — Thu, 09 Nov 2006 07:36:49 GMT

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 '\';
"

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

Hein van den Heuvel — Thu, 09 Nov 2006 07:53:47 GMT

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.

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

Eric Antunes — Thu, 09 Nov 2006 09:11:23 GMT

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

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

Frank de Vries — Mon, 13 Nov 2006 01:43:49 GMT

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