String List

Being a PLSQL developer many a times I have been in a situation where I would get list of values as input in a string and have to check if they exist in a particular table. The values would be separated by a delimiter. Let’s consider the below example

String Input: ‘ORACLE, SQL SERVER, DB2, MYSQL, SYBASE’

I have to use this string to see if these values exist in the table.

One possible way is to build a dynamic SQL. Just pad single quotes before and after each value in the string and build a SQL with the IN clause. But I want to do it without a dynamic SQL. How is this possible?

I came up with this SQL which splits this string and gives me the values in the string as rows.

Here is the SQL…

SQL> var a varchar2(100)

SQL> exec :a := 'ORACLE, SQL SERVER, DB2, MYSQL, SYBASE'

PL/SQL procedure successfully completed.

SQL>  SELECT TRIM(SUBSTR(:a, INSTR(','||:a,',',1,level), INSTR(:a||',',',',1,level) - INSTR(','||:a,',',1,level))) my_list
  2        FROM dual, (SELECT length(replace(translate(lower(:a)||',','abcdefghijklmnopqrstuvwxyz1234567890',' '),' ','')) cnt
  3                                    FROM dual)
  4  CONNECT BY level <= cnt
  5  /

MY_LIST
--------------------------------------------------------------------------------
ORACLE
SQL SERVER
DB2
MYSQL
SYBASE

So what have I done here? Let me split it into steps for your understanding.

1. I am identifying the possible letters that would appear in the values and translating them into blank space. Following command does that for me.

SQL> SELECT translate(lower(:a)||',','abcdefghijklmnopqrstuvwxyz1234567890',' ') val
   2       FROM dual;

VAL
--------------------------------------------------------------------------------
 ,  , , ,  ,

Now you can see I don’t have anything other than comma (The delimiter).

2. Now I am going to remove all the blank space from the output that I got in step 1.

SQL> SELECT replace(translate(lower(:a)||',','abcdefghijklmnopqrstuvwxyz1234567890',' '),' ','') val         
   2      FROM dual;

VAL
--------------------------------------------------------------------------------
,,,,,

Now the length of the output will give me the total number of values in the list. Note that I have padded a extra comma to the string as the number of delimiter is always less than the number of values by one.

3. So the third step is obviously to count the number of comma in the above input and determine total number of values in the string list.

SQL> SELECT length(replace(translate(lower(:a)||',','abcdefghijklmnopqrstuvwxyz1234567890',' '),' ','')) cnt
  2       FROM dual
  3  /

       CNT
----------
            5

4. Next step is to generate the string list for 5 times in rows using the CONNECT BY clause.

SQL> SELECT level, :a my_list
  2       FROM dual, (SELECT length(replace(translate(lower(:a)||',','abcdefghijklmnopqrstuvwxyz1234567890',' '),' ','')) cnt
  3                               FROM dual)
  4  CONNECT BY level <= cnt
  5  /

     LEVEL MY_LIST
---------- ---------------------------------------------------------------------------------------------------------------
         1 ORACLE, SQL SERVER, DB2, MYSQL, SYBASE
         2 ORACLE, SQL SERVER, DB2, MYSQL, SYBASE
         3 ORACLE, SQL SERVER, DB2, MYSQL, SYBASE
         4 ORACLE, SQL SERVER, DB2, MYSQL, SYBASE
         5 ORACLE, SQL SERVER, DB2, MYSQL, SYBASE

5. Now its simple with the help of SUBSTR and INSTR I can split each values as shown below.

SQL>  SELECT TRIM(SUBSTR(:a, INSTR(','||:a,',',1,level), INSTR(:a||',',',',1,level) - INSTR(',':a,',',1,level))) my_list
  2        FROM dual, (SELECT length(replace(translate(lower(:a)',','abcdefghijklmnopqrstuvwxyz1234567890',' '),' ','')) cnt
  3                            FROM dual)
  4  CONNECT BY level <= cnt
  5  /

MY_LIST
--------------------------------------------------------------------------------
ORACLE
SQL SERVER
DB2
MYSQL
SYBASE

So now I can use this SQL to convert any value list into rows and use in my SQL statements.

And if you can use Regular expression things can be much more easier.

Check this out.

SQL>  SELECT trim(regexp_substr(:a,'[^,]+', 1, level)) my_list
  2     FROM dual
  3  CONNECT BY level <= NVL( LENGTH( REGEXP_REPLACE( :a, '[^,]+', NULL ) ), 0 ) + 1
  4  /

MY_LIST
--------------------------------------------------------------------------------
ORACLE
SQL SERVER
DB2
MYSQL
SYBASE

NOTE: This might need have to be customized based on the given requirement.