PLSQL newbie question [message #431875] |
Thu, 19 November 2009 13:54  |
harish7447
Messages: 22 Registered: July 2009
|
Junior Member |
|
|
Hi All
I have a pl/sql procedure in which i use dynamic sql. In that I have:
if (name is not null) then
sql_query := sql_query || 'and lower(nvl(dp.NAME,''%'')) like lower(TRIM(:name))';
end if;
This is the part of the where clause where it appends to the where clause if the name is not null. The name is being passed as an In parameter.
It looks shabby, I know . But excuse this newbie.
This works fine now if I pass a single word to the name. But the issue is we need to search by multiple words separated by commas.
For example the SQL would be
and name like ('harish') or name like ('kevin') or name like ('harry')
So now I would be passing the Name as a string separated by commas. How can I change the pl/sql code accordingly to separate the string delimited by commas and use like or multiple times. Please help me on this.
Hope I'm clear with my question
Thanks
Harish
|
|
|
|
|
|
Re: PLSQL newbie question [message #431882 is a reply to message #431876] |
Thu, 19 November 2009 14:49   |
harish7447
Messages: 22 Registered: July 2009
|
Junior Member |
|
|
BlackSwan wrote on Thu, 19 November 2009 15:01name in ('harish','kevin','harry')
Thanks for your reply. But I dont pass in whole names all the time, but only part of the name and so I need to search using like. Thanks again
|
|
|
Re: PLSQL newbie question [message #431884 is a reply to message #431882] |
Thu, 19 November 2009 15:26  |
Its_me_ved
Messages: 979 Registered: October 2009 Location: India
|
Senior Member |
|
|
harish7447 wrote on Thu, 19 November 2009 14:49BlackSwan wrote on Thu, 19 November 2009 15:01name in ('harish','kevin','harry')
Thanks for your reply. But I dont pass in whole names all the time, but only part of the name and so I need to search using like. Thanks again
Quote:
So now I would be passing the Name as a string separated by commas
The appropriate solution should be
Quote:
Search for "varying inlist", here and on AskTom.
Regards
Michel
|
|
|