Home » SQL & PL/SQL » SQL & PL/SQL » don't get any rows when given 'like' condition
don't get any rows when given 'like' condition [message #219936] Fri, 16 February 2007 16:37 Go to next message
pzlj6x
Messages: 107
Registered: May 2005
Location: Louisville
Senior Member
I know it sounds stupid and silly when I ask you this question, but this is something I don't understand

I am doing a simple query to get the records but not getting what I want.

select count(*)
from all_objects
where object_name like 'X_AR%'

I get count = 266

and when I modify this to

select count(*)
from all_objects
where object_name like 'X_AR_%'

I still get the same count result as 266.

it does not give me count for the tables that start with X_AR_*
When I see the list
I see tables like X_ARG_* , X_ARMT_* and etc. and also I see tables X_AR_AMT, X_AR_PCT. I wanted to get the count only for X_AR_* tables. is it something sql does not understand the underscore before the % symbol... or Am I missing something.

here is my test case

SQL> create table t_tet
2 (fld1 char(10));

Table created.

SQL> create table t_te_try
2 (fld1 char(10));

Table created.

SQL> select count(*) from all_objects
2 where object_name like 'T_TE_%';


COUNT(*)
----------
2

However if my table name is something like this T_TEST and T_TEST_TRY then I get correct result


SQL> create table t_test
2 (fld1 char(10));

Table created.

SQL> create table t_test_try
2 (fld1 char(10));

Table created.

SQL> select count(*) from all_objects
2 where object_name like 'T_TEST_%';


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

Can anyone explain that...
Thanks
Ravi
Re: don't get any rows when given 'like' condition [message #219937 is a reply to message #219936] Fri, 16 February 2007 16:47 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Can anyone explain that...
I suspect that you have not read the fine manual to learn & understand that the underscore character is a metacharter for match any single character much like the percent sign is a metacharacter for match 1 or MORE characters.

You'll need to "escape" the "_" so it is take literally & not used as wild card character.

You can find the fine manuals at http://tahiti.oracle.com
Re: don't get any rows when given 'like' condition [message #219939 is a reply to message #219936] Fri, 16 February 2007 16:59 Go to previous messageGo to next message
pzlj6x
Messages: 107
Registered: May 2005
Location: Louisville
Senior Member
Thanks for the reply. Guess, I have not read the complete manual.
Any work around for this.
Thanks in advance
Re: don't get any rows when given 'like' condition [message #219942 is a reply to message #219936] Fri, 16 February 2007 17:03 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Any work around for this.
Are you incapable or unwilling to Read The Fine Manuals?
You'll need to "escape" the "_" so it is take literally & not used as wild card character.

You can find the fine manuals at http://tahiti.oracle.com
http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/conditions007.htm#sthref2803


Why do you expect to be spoon fed information which is readily available to you?

[Updated on: Fri, 16 February 2007 17:04] by Moderator

Report message to a moderator

Re: don't get any rows when given 'like' condition [message #219947 is a reply to message #219936] Fri, 16 February 2007 18:25 Go to previous messageGo to next message
pzlj6x
Messages: 107
Registered: May 2005
Location: Louisville
Senior Member
Hello, Thanks for the reply.

>Are you incapable or unwilling to Read The Fine Manuals?

Well, given enough time to read the manual, I can surely find out and I know it's somewhere in the manual..... but it does not work out when someone and something really urgent and which can not wait anylonger by the customer, that's when you look for someone who can get a quick solution to a small problem. I am sure you must have come accross many tense situations where you don't get much time to research.... Something for you to think about....

Once Again Thanks for the instant help.
Regards



Re: don't get any rows when given 'like' condition [message #219979 is a reply to message #219947] Sat, 17 February 2007 02:45 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If the customer was waiting for it, meaning it would go straight into production, I would never take any unknown person's word for it.
Even then, I would look it up in the manual to check for unwanted (and unmentioned) side-effects.
Anacedent mentioned:
Quote:
You'll need to "escape" the "_" so it is take literally & not used as wild card character.

Based on the url he provided, searching for escape returned quite a lot of hits.
Considering the fact you were using it in a sql-statement, I followed the Database 9i link, and then the SQL Reference link.
I found the answer in under 2 minutes.

What I am trying to explain is that you really should get comfortable searching the manuals. I will not say you have to read them all, but make it a habit first to try and search tahiti before any other source.
You will notice that if you feel 'at home' in the manuals, it becomes quite easy to find the answer to life, the universe and everything.
Re: don't get any rows when given 'like' condition [message #220012 is a reply to message #219947] Sat, 17 February 2007 11:13 Go to previous message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
raviganti wrote on Sat, 17 February 2007 00:25
Well, given enough time to read the manual, I can surely find out and I know it's somewhere in the manual..... but it does not work out when someone and something really urgent and which can not wait anylonger by the customer, that's when you look for someone who can get a quick solution to a small problem. I am sure you must have come accross many tense situations where you don't get much time to research.... Something for you to think about....

Nobody is saying you have to read and memorise the entire Oracle documentation set. Surely anyone developing with Oracle has something like the following set up in their browser. I don't really see how anyone could work without it:

http://www.williamrobertson.pwp.blueyonder.co.uk/jpegs/firefox-oracle-rtfm.jpg
Previous Topic: OUTER UNION CORR
Next Topic: ORA-6550, 904 when I create a function
Goto Forum:
  


Current Time: Sat Dec 10 22:40:37 CST 2016

Total time taken to generate the page: 0.08390 seconds