Thanks for the question, Partheeban.

Asked: February 26, 2017 - 6:54 pm UTC

Last updated: March 04, 2017 - 1:51 am UTC

Version: Latest

Viewed 1000+ times

You Asked

create table t (id number, name varchar2(30))

insert into t (111, 'MGR_123 NAME 123');

insert into t (123, 'Silvaji_521 main 234');

insert into t (786, 'Rajini_786 sounth 111');

insert into t (678, 'vIKRAm_333 nila 532441');

insert into t (987, 'SURYA_55 jo 11231');

Can we pass Multiple partial search values in the single query in SQL? or can we do in PLSQL?

my procedure is having only one input value is passing based on that I have to return the table value. that input value can provide multiple partial values. can u pls provide the solution on this?

input_Value = mgr, soun,5324 ,

Is it possible to create the query like below?
SELECT * FROM T
WHERE UPPER(NAME) LIKE UPPER(:input_Value);

I need a output like below

ID NAME
---- ------
111 MGR_123 NAME 123
786 Rajini_786 sounth 111
678 vIKRAm_333 nila 532441

Thanks
JPartheeban

and Connor said...

You checked that test case right ? :-)

SQL> insert into t (111, 'MGR_123 NAME 123');
insert into t (111, 'MGR_123 NAME 123')
               *
ERROR at line 1:
ORA-00928: missing SELECT keyword


SQL> insert into t (123, 'Silvaji_521 main 234');
insert into t (123, 'Silvaji_521 main 234')
               *
ERROR at line 1:
ORA-00928: missing SELECT keyword


SQL> insert into t (786, 'Rajini_786 sounth 111');
insert into t (786, 'Rajini_786 sounth 111')
               *
ERROR at line 1:
ORA-00928: missing SELECT keyword


SQL> insert into t (678, 'vIKRAm_333 nila 532441');
insert into t (678, 'vIKRAm_333 nila 532441')
               *
ERROR at line 1:
ORA-00928: missing SELECT keyword


SQL> insert into t (987, 'SURYA_55 jo 11231');
insert into t (987, 'SURYA_55 jo 11231')
               *
ERROR at line 1:
ORA-00928: missing SELECT keyword

Anyway.... you can convert a string into a set of rows via a number of means, for example:

SQL> variable str varchar2(100)
SQL> exec :str := 'mgr,soun,5324,';

PL/SQL procedure successfully completed.

SQL>
SQL> select substr(:str,
  2              loc,nvl(
  3              lead(loc) over ( order by loc ) – loc-1,
  4              length(:str)-loc)
  5             ) list_as_rows
  6      from (
  7        select distinct (instr(','||:str,',',1,level)) loc
  8        from dual
  9        connect by level <= length(:str)-length(replace(:str,','))
 10       );

LIST_AS_ROWS
---------------------------------------------------------------------
mgr
soun
5324

And once you've done that, its just a case of doing a normal join

SQL> create table t (id number, name varchar2(30));

Table created.

SQL>
SQL> insert into t values(111, 'MGR_123 NAME 123');

1 row created.

SQL> insert into t values(123, 'Silvaji_521 main 234');

1 row created.

SQL> insert into t values(786, 'Rajini_786 sounth 111');

1 row created.

SQL> insert into t values(678, 'vIKRAm_333 nila 532441');

1 row created.

SQL> insert into t values(987, 'SURYA_55 jo 11231');

1 row created.

SQL>
SQL> variable str varchar2(100)
SQL> exec :str := 'mgr,soun,5324,';

PL/SQL procedure successfully completed.

SQL>
SQL> with x as
  2  (
  3  select substr(:str,
  4              loc,nvl(
  5              lead(loc) over ( order by loc ) – loc-1,
  6              length(:str)-loc)
  7             ) list_as_rows
  8      from (
  9        select distinct (instr(','||:str,',',1,level)) loc
 10        from dual
 11        connect by level <= length(:str)-length(replace(:str,','))
 12       )
 13  )
 14  select *
 15  from t, x
 16  where instr(name,list_as_rows) > 0;

        ID NAME                           LIST_AS_ROWS
---------- ------------------------------ --------------------------------------------------------------------------------------------------------------------------------
       786 Rajini_786 sounth 111          soun
       678 vIKRAm_333 nila 532441         5324

2 rows selected.

SQL>
SQL>
SQL>
SQL>

Use upper/lower where appropriate if you want case insensitive checks

Rating

(5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Multiple Partial Search in single field

JPartheeban, February 27, 2017 - 3:01 pm UTC

Thanks Connor McDonald, Thanks for the solution.

Sorry Connor, I written the test case when I was in midnight oil burn stage with sleepy eyes at 2AM(Midnight) India IST. I will always provide useful test case to this site. This time I really sorry about that.

Thanks again for the solution

Regards
JPartheeban

February 27, 2017 - 3:49 pm UTC

FYI, If you're looking for a quick, easy way to generate insert statements, add the insert comment when selecting your data in SQL Developer:

with rws as (
  select rownum x from DUAL connect by level <= 10
)
  select /*insert*/x from rws;

Insert into DUAL (X) values (1);
Insert into DUAL (X) values (2);
Insert into DUAL (X) values (3);
Insert into DUAL (X) values (4);
Insert into DUAL (X) values (5);
Insert into DUAL (X) values (6);
Insert into DUAL (X) values (7);
Insert into DUAL (X) values (8);
Insert into DUAL (X) values (9);
Insert into DUAL (X) values (10);

few more details

Rajeshwaran, Jeyabal, February 27, 2017 - 4:42 pm UTC

....
If you're looking for a quick, easy way to generate insert statements, add the insert comment when selecting your data in SQL Developer: 
....

BTW, when you place this sql code to the worksheet in SQL Developer, press F5 execute or "Run as script" option from the menu, to generate the INSERT statements.

with rws as (
  select rownum x from DUAL connect by level <= 10
)
  select /*insert*/x from rws;

else with SQLCI

demo@ORA12C> with rws as (
  2    select rownum x from DUAL connect by level <= 10
  3  )
  4    select /*insert*/x from rws;
REM INSERTING into DUAL
SET DEFINE OFF;
Insert into DUAL (X) values (1);
Insert into DUAL (X) values (2);
Insert into DUAL (X) values (3);
Insert into DUAL (X) values (4);
Insert into DUAL (X) values (5);
Insert into DUAL (X) values (6);
Insert into DUAL (X) values (7);
Insert into DUAL (X) values (8);
Insert into DUAL (X) values (9);
Insert into DUAL (X) values (10);

10 rows selected.


demo@ORA12C> set sqlformat insert

demo@ORA12C> with rws as (
  2    select rownum x from DUAL connect by level <= 10
  3  )
  4  select x from rws ;
REM INSERTING into DUAL
SET DEFINE OFF;
Insert into DUAL (X) values (1);
Insert into DUAL (X) values (2);
Insert into DUAL (X) values (3);
Insert into DUAL (X) values (4);
Insert into DUAL (X) values (5);
Insert into DUAL (X) values (6);
Insert into DUAL (X) values (7);
Insert into DUAL (X) values (8);
Insert into DUAL (X) values (9);
Insert into DUAL (X) values (10);

10 rows selected.


demo@ORA12C>

few more details

JPartheeban, February 28, 2017 - 3:13 pm UTC

Thanks Chris,

Regards
JPartheeban

Multiple Partial Search in single field

JPartheeban, March 03, 2017 - 1:04 pm UTC

Hi Connor McDonald/Chris

If clients are new to front end applications and from frontend he is entering the spaces after and before comma! So during the time how we can handle this situation like below

exec :str := ' mgr_123 Na , Rajini_786 sou , la 5324 ';

create table t (id number, name varchar2(30));

insert into t values(111, 'MGR_123 NAME 123');

insert into t values(123, 'Silvaji_521 main 234');

insert into t values(786, 'Rajini_786 sountharya 111');

insert into t values(678, 'vIKRAm_333 nila 532441');

insert into t values(987, 'SURYA_55 jo 11231');

We got received the Client Inputs from front end like below value
exec :str := ' mgr_123 Na , Rajini_786 sou , la 5324 ';

ID NAME LIST_AS_ROWS
---------- ------------------------------ ----------------------
111 MGR_123 NAME 123 mgr_123 Na
786 Rajini_786 sounth 111 Rajini_786 sou
678 vIKRAm_333 nila 532441 la 5324

we have to remove the
* we have to remove the spaces before starting the characters
* we have to remove the spaces before and after the comma.
* we have to remove the after the last characters
* We DONT need to remove spaces which is exists between characters?

Thanks
JPartheeban

March 04, 2017 - 1:51 am UTC

Split it by comma first like already done, and then just add rtrim, ltrim or both around the "list_as_rows" column

Multiple Partial Search in single field

A reader, March 07, 2017 - 1:44 pm UTC

Thanks Conner

Regards
JPartheeban

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Multiple Partial Search in single field

few more details

few more details

Multiple Partial Search in single field

Multiple Partial Search in single field

More to Explore

Analytics