Home » SQL & PL/SQL » SQL & PL/SQL » wild card search
icon4.gif  wild card search [message #226935] Tue, 27 March 2007 00:10 Go to next message
aditiC
Messages: 38
Registered: February 2006
Location: INDIA
Member
Hi All,

I need to do wild card search on a table.
i wrote the query as below:

select * from table_nm
where lower(col_1_name) like '%'
and lower(col_2_name) like '%'
and lower(col_3_name) like '%'
and lower(col_4_name) like '%'
and lower(col_5_name) like '%'
and lower(col_6_name) like '%'

col_4_name & col_5_name are not null cols.
all other cols can hv null values.

i am unable to retrieve all the recs if col_1_name,col_2_name,
col_3_name or col_5_name are having null values.
But i need to retrieve them irrespective of whether null value is there or not.

I can't use 'or' in place of 'and'.
Pls suggest something.

Thanks in advance
Re: wild card search [message #226937 is a reply to message #226935] Tue, 27 March 2007 00:12 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
select * from table_nm;
Re: wild card search [message #226952 is a reply to message #226937] Tue, 27 March 2007 00:27 Go to previous messageGo to next message
aditiC
Messages: 38
Registered: February 2006
Location: INDIA
Member
Hi anacedent,

actually i am having the fields in the front end through which i am entering the values so need to mention them in the where clause.
Re: wild card search [message #226957 is a reply to message #226935] Tue, 27 March 2007 00:32 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>so need to mention them in the where clause.
WHY if your are trying to totally wild card everything?
How will the row counts differ between the 2 SELECTs?
Re: wild card search [message #226982 is a reply to message #226957] Tue, 27 March 2007 01:00 Go to previous messageGo to next message
aditiC
Messages: 38
Registered: February 2006
Location: INDIA
Member
It can be either total wild card or he can enter values for some of the fields through the front end gui.
Re: wild card search [message #227086 is a reply to message #226935] Tue, 27 March 2007 05:37 Go to previous messageGo to next message
gintsp
Messages: 118
Registered: February 2007
Senior Member
Here is technique how to do that using bind variables
http://asktom.oracle.com/pls/asktom/f?p=100:11:2251579471302379::::P11_QUESTION_ID:1288401763279

And here is what I think of using too much wildcards
http://gplivna.blogspot.com/2007/03/where-bad-performance-starts-my.html

Gints Plivna
http://www.gplivna.eu
Re: wild card search [message #227410 is a reply to message #227086] Wed, 28 March 2007 03:23 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
select * from table_nm
where lower(col_1_name) like '%'
or col_1_name is null
and lower(col_2_name) like '%'
or col_2_name like '%'
and lower(col_3_name) like '%'
or col_3_name like '%'
and lower(col_4_name) like '%'
or col_4_name like '%'
and lower(col_5_name) like '%'
or col_5_name like '%'
and lower(col_6_name) like '%'
or col_6_name like '%'
Re: wild card search [message #227411 is a reply to message #227410] Wed, 28 March 2007 03:27 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You can not mix ands and ors without parentheses.
Re: wild card search [message #227412 is a reply to message #227410] Wed, 28 March 2007 03:27 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
The horror! I'd say an NVL is in its place here Wink

MHE
Re: wild card search [message #227631 is a reply to message #227410] Wed, 28 March 2007 17:59 Go to previous message
gintsp
Messages: 118
Registered: February 2007
Senior Member
muzahidul islam wrote on Wed, 28 March 2007 11:23
select * from table_nm
where lower(col_1_name) like '%'
or col_1_name is null
and lower(col_2_name) like '%'
or col_2_name like '%'
and lower(col_3_name) like '%'
or col_3_name like '%'
and lower(col_4_name) like '%'
or col_4_name like '%'
and lower(col_5_name) like '%'
or col_5_name like '%'
and lower(col_6_name) like '%'
or col_6_name like '%'


And what do you want to say with that?
Have you read the asktom thread I provided?
Reviewer: Bill Coulam from CO, USA suggested similar approach and you can see what the deficiences of it are in that thread.

Gints Plivna
http://www.gplivna.eu
Previous Topic: Help w/ DECODE
Next Topic: data encryption
Goto Forum:
  


Current Time: Sun Dec 04 08:53:38 CST 2016

Total time taken to generate the page: 0.04402 seconds