Getting the Most Out of INSTR as an Alternative to Using the IN and NOT IN Operator

Reprinted with Permission by Quest Software Feb. 2002

Getting the most out of INSTR as an alternative to using the IN and NOT IN operator.
By Grant Daley (gdaley@cuscal.com.au) and Brahmaiah Koniki (bkoniki@cuscal.com.au)

Many people may already know that you can use the INSTR operator as an alternative to using IN and NOT IN where he search column contains alpha data.

For Example:
We have a simple table that contains Australian Political Parties.

SQL> DESC POLITICAL_PARTY
Name                                      Null?    Type
----------------------------------------- -------- ---------------
PARTY_ID                                  NOT NULL NUMBER(7)
PARTY_SNAME                               NOT NULL VARCHAR2(10)
PARTY_NAME                                NOT NULL VARCHAR2(40)
MAJOR_PARTY_FLAG                          NOT NULL VARCHAR2(1)

If we wanted to know the PARTY_ID of the political party representing the conservationists in the Federal sphere of government, we can use :

SQL> select party_id, party_name
2 from political_party
3 where instr('The Greens',party_name) > 0;

PARTY_ID PARTY_NAME
---------- ----------------------------------------
20 The Greens

It is more likely though that we will have an application built in Developer6 or equivalent that already has the parties listed and our users select which party or parties they want to pull data about.

The check box in the form will return the PARTY_ID for use in other queries.

Thus we will end-up with a query that is the equivalent of this :

SQL> select party_id, party_name
2 from political_party
3 where instr('1,22',party_id) > 0;

Unfortunately, we get more than we wanted !

PARTY_ID   PARTY_NAME
---------- ------------------------------
         1 Australian Labor Party
         2 Democrats
        22 Unity

3 rows selected.

which somehow always disappoints the users !

So here is our first tip :
To overcome this you need to ensure that INSTR can compare each comma delimited variable with the equivalent comma delimited variable from the table.

Building on the above problematic example, this will give us :

SQL> select party_id, party_name
2 from political_party
3 where instr(',1,22,',','||party_id||',') > 0;

INSTR will now use the ',1,22,' part to compare with the ','||party_id||',' part, giving :

PARTY_ID   PARTY_NAME
---------- ----------------------------------------
         1 Australian Labor Party
        22 Unity

2 rows selected.

which is exactly what we wanted.

Our second tip for INSTR is for those occasions you might want to easily include or exclude or effectively ignore the elements in the string.

We know from above that stating :
where instr(',1,22,',','||party_id||',') > 0
gives us the elements in the list, but what if we want to ignore 1 and 22 ?

Observe the subtle difference below :

SQL> select party_id, party_name
2 from political_party
3 where instr(',1,22,',','||party_id||',') = 0;

Oracle returns all political parties that do not have and PARTY_ID of 1 or 22.

PARTY_ID   PARTY_NAME
---------- ----------------------------------------
         2 Democrats
         3 Liberal Party
         4 National Party
         5 ACT Greens
         6 Australian Greens
         7 Better Future For Our Children
         8 Christian Democratic Party
         9 Greens Western Australia
        10 Independent
        11 Independent Australian Labor Party
        12 Independent Liberal
        13 No Pokies Party
        14 One Nation
        15 Outdoor Recreation Party
        16 Reform The Legal System
        17 Shooters Party
        18 South Australia First
        19 Tasmanian Greens
        20 The Greens
        21 United Canberra Party
        23 NT Country Liberal Party

21 rows selected.

There is no prizes for guessing what using
where instr(',1,22,',','||party_id||',') >= 0;
will give us.

This is fine if you are a lazy programmer. But we like it when our code is smart enough to cope with the subtle differences, and as Ganesh and God knows, we can go on writing endless cursors or whatever, which brings us to our third and final tip :

The thing about INSTR is that when it finds a match it returns a number > 0 and when there is no match it returns a zero.

Thus, if we introduce our old friend DECODE to INSTR we have the means to make the process of including and excluding lists of values soft.

For example :

SQL> select party_id, party_name,
2 instr(',1,22,',','||party_id||',') instring
3 from political_party
4 where decode(instr(',1,22,',','||party_id||','),0,0,1) > 0;

gives us :

PARTY_ID   PARTY_NAME INSTRING
---------- ---------------------------------------- --------
         1 Australian Labor Party 1
        22 Unity 3

2 rows selected.

while

SQL> select party_id, party_name,
2 instr(',1,22,',','||party_id||',') instring
3 from political_party
4 where decode(instr(',1,22,',','||party_id||','),0,1,0) > 0;

gives us everything but PARTY_ID 1 and 22.

PARTY_ID ---------- ---------------------------------------- --------
2 Democrats 3 Liberal Party 4 National Party 5 ACT Greens 6 Australian Greens 7 Better 8 Christian Democratic Party 9 Greens Western Australia 10 Independent 11 Independent 12 Independent Liberal 13 No Pokies Party 14 One Nation 15 Outdoor Recreation Party 16 Reform The Legal System 17 Shooters Party 18 South Australia First 19 Tasmanian Greens 20 The Greens 21 United Canberra Party 23 NT Country Liberal Party PARTY_NAME INSTRING
0
0
0
0
0
Future For Our Children 0
0
0
0
Australian Labor Party 0
0
0
0
0
0
0
0
0
0
0
0

21 rows selected.

We reckon you will have the skill to work out what would give us all 23 rows.

From here you will be able to parameterise as required to make the whole thing very flexible.

For example :

CURSOR c1 (party_id_str IN VARCHAR2,
           instr_true IN NUMBER,
           instr_false IN NUMBER) IS
      select party_id, party_name,
             instr(party_id_str,','||party_id||',') instring
      from political_party
      where decode(instr(party_id_str,',
                '||party_id||','),0, instr_true, instr_false) > 0;

Good luck !