OraFAQ Forum: SQL & PL/SQL » Using INSTR in Where clause to give COMPLETE MATCH strings

Home » SQL & PL/SQL » SQL & PL/SQL » Using INSTR in Where clause to give COMPLETE MATCH strings (10g, Windows)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Using INSTR in Where clause to give COMPLETE MATCH strings [message #534885]

Fri, 09 December 2011 23:49

ninan
Messages: 163
Registered: June 2011
Location: Noida

Senior Member

We have a SSRS Front end screen which sends multi-select column values as comma separated strings to back end ( Oracle 10g)
procedure .

The procedure builds the string by inserting single quotes in the following manner.

P_BU_LST is the parameter which have comma separated values

'1234,3456,4577' i.e, BU ids selected by user in front end
the procedure inserts single quotes to this paramer value

i.e., '1234','3456','4577'

  v_bu_lst      := '''' || REPLACE(v_selbu, ',', ''',''')|| '''';

This is used the where clause of the REF CURSOR SELECT query which send the data back to SSRS

ie.,


SELECT BU.*
FROM BU_DETAIL BU
WHERE INSTR(V_BU_LST,BU_ID) <> 0;

INSTR has a chance to fail in this scenario if the value send from the front end is 123456,3456,4577

here 123456 does not exist in table, but it will be true for INSTR and values 1234 from table will be send back to SSRS which is wrong. Earlier I was using a function to convert the comma separated values to multi-rows and treat it like a lookup table.

But the main table has around million records , and each row has to processed against each row of lookup table, which makes it slower. To avoid this I used INSTR which is faster but can give wrong results.

Can anyone advise, how to avoid this wrong values and filter only the fully matched string using INSTR.

Report message to a moderator

Re: Using INSTR in Where clause to give COMPLETE MATCH strings [message #534892 is a reply to message #534885]

Fri, 09 December 2011 23:59

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Your design is very bad.

Include ' and , in your search string (bu_id).
In addition, I don't understand why you need ' around numbers!

Regards
Michel

Report message to a moderator

Re: Using INSTR in Where clause to give COMPLETE MATCH strings [message #534894 is a reply to message #534892]

Sat, 10 December 2011 00:14

ninan
Messages: 163
Registered: June 2011
Location: Noida

Senior Member

Thanks, I was thinking about the same to add like this



SELECT BU.*
FROM BU_DETAIL BU
WHERE INSTR(V_BU_LST,''''||BU_ID||'''') <> 0;

OR

SELECT BU.*
FROM BU_DETAIL BU
WHERE INSTR(V_BU_LST,BU_ID) <> 0 AND BU.BU_ID=BU.BU_ID;

Quote:

In addition, I don't understand why you need ' around numbers!

Because of INSTR , I had to add '.

Report message to a moderator

Re: Using INSTR in Where clause to give COMPLETE MATCH strings [message #534897 is a reply to message #534894]

Sat, 10 December 2011 00:22

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

No, you can add ',' in the search string as you already add it.

Regards
Michel

Report message to a moderator

Re: Using INSTR in Where clause to give COMPLETE MATCH strings [message #534902 is a reply to message #534894]

Sat, 10 December 2011 00:45

flyboy
Messages: 1903
Registered: November 2006

Senior Member

Maybe you would get the corect expresions if you wrote the content of both parameters of the INSTR functions, e.g.

INSTR('123456,3456,4577','1234')
INSTR('''123456'',''3456'',''4577''','''1234''')
INSTR(',123456,3456,4577,',',1234,')

The second one should be correct too, but I do not know if you used it exactly this way.
However I would use the third variant. There would be no need for single quotes inside even when working with strings - they are identified by the separator (comma) in V_BU_LST. Its only effect would be restricting single quote from the supported content of the searched string.

Report message to a moderator

Re: Using INSTR in Where clause to give COMPLETE MATCH strings [message #534903 is a reply to message #534897]

Sat, 10 December 2011 00:48

ninan
Messages: 163
Registered: June 2011
Location: Noida

Senior Member

Thanks for the suggestion. It was so simple!!!!

Now I modified the code to


p_bu_lst is having value '3434,3535'


v_bu_lst      := p_bu_lst || ',';

SELECT BU.*
FROM item_test1 BU
INSTR(V_BU_LST,NVL(BU_ID,0)||',') <> 0;


So the search criteria will be 

WHERE INSTR(3434,3535,',BU_ID||',') <> 0;

Since BU_ID can have alphanumeric values

it will not work the following way. Table have value '123DAO'

WHERE INSTR('123DAOJ,123DAOA,',BU_ID) <> 0;

It works if I change it to 

WHERE INSTR('123DAOJ,123DAOA,',BU_ID||',') <> 0;

But does not work if the BU_ID is NULL in table.

so finally had to change it to 

WHERE INSTR('123DAOJ,123DAOA,',NVL(BU_ID,0)||',') <> 0;

This works. Thanks

Report message to a moderator

Re: Using INSTR in Where clause to give COMPLETE MATCH strings [message #534904 is a reply to message #534903]

Sat, 10 December 2011 00:54

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

v_bu_lst := p_bu_lst || ','

Quote:

INSTR('123DAOJ,123DAOA,',BU_ID||',') <> 0;

Use

v_bu_lst      := ',' || p_bu_lst || ','
INSTR('123DAOJ,123DAOA,',','||BU_ID||',') <> 0;

Then you also cover both side matching and this also works for null.
If you only use right ',' you will get wrong matching of 123456 for 456 searched string

Regards
Michel

Report message to a moderator