Home » SQL & PL/SQL » SQL & PL/SQL » PLSQL newbie question (10g)
PLSQL newbie question [message #431875] Thu, 19 November 2009 13:54 Go to next message
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 Smile. 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 #431876 is a reply to message #431875] Thu, 19 November 2009 14:01 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
name in ('harish','kevin','harry')
Re: PLSQL newbie question [message #431877 is a reply to message #431875] Thu, 19 November 2009 14:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Search for "varying inlist", here and on AskTom.

Regards
Michel
Re: PLSQL newbie question [message #431879 is a reply to message #431875] Thu, 19 November 2009 14:09 Go to previous messageGo to next message
rmurali4u
Messages: 2
Registered: November 2009
Location: US
Junior Member

Hi Harish,

There is PL/SQL Code which tokenize the string with the delimiter.

Search in google with "pl sql string tokenizer" you will get an code for string tokenizer.

So by using this you can get all the names separated by commas.

Once you have all the names and you can loop through the logic for each name.

Murali
Re: PLSQL newbie question [message #431882 is a reply to message #431876] Thu, 19 November 2009 14:49 Go to previous messageGo to next message
harish7447
Messages: 22
Registered: July 2009
Junior Member
BlackSwan wrote on Thu, 19 November 2009 15:01
name 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 Go to previous message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
harish7447 wrote on Thu, 19 November 2009 14:49
BlackSwan wrote on Thu, 19 November 2009 15:01
name 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

Previous Topic: have some problems with my sql code in oracle
Next Topic: Cursor Help with a Select Statement
Goto Forum:
  


Current Time: Thu Feb 06 16:29:12 CST 2025