CODEWITHSUNDEEP
oracle-databaseplsqloracle11g
Léster
Léster

Reputation: 1279

How to convert delimited string to a PL/SQL table for JOINing?

I have the following table:

CREATE TABLE T_DATA
(
    id VARCHAR2(20),
    value VARCHAR2(30),
    index NUMBER,
    valid_from DATE,
    entry_state VARCHAR2(1),
    CONSTRAINT PK_T_DATA PRIMARY KEY(id, value)
);

and I have the following string:

id1:value1,id2:value2,id3:value3...

where id and value are actually corresponding values on T_DATA. I'm expected to use that string and return a resultset from T_DATA usind the ids and values provided as filters (basically, a select). I was told I can convert the string into a PL/SQL table with the two columns and with that, a simple SELECT * FROM T_DATA INNER JOIN [PL/SQL table] ON [fields] will retrieve the rows required, but I can't find out how to convert the string to a PL/SQL table with multiple columns. How can I do it?

Upvotes: 0

Views: 494

Answers (2)

EJ Egyed
EJ Egyed

Reputation: 6094

The simplest solution I can think of (although it may not be the most efficient) is to just use a simple INSTR

WITH
    t_data
    AS
        (    SELECT 'id' || ROWNUM        AS id,
                    'value' || ROWNUM     AS VALUE,
                    ROWNUM                AS index_num,
                    SYSDATE - ROWNUM      AS valid_from,
                    'A'                   AS entry_state
               FROM DUAL
         CONNECT BY LEVEL <= 10)
SELECT *
  FROM t_data
 WHERE INSTR ('id1:value1,id3:value3', id || ':' || VALUE) > 0;

If you want to split the search string, you can try a query like this one

WITH
    t_data
    AS
        (    SELECT 'id' || ROWNUM        AS id,
                    'value' || ROWNUM     AS VALUE,
                    ROWNUM                AS index_num,
                    SYSDATE - ROWNUM      AS valid_from,
                    'A'                   AS entry_state
               FROM DUAL
         CONNECT BY LEVEL <= 10),
  split_string AS (SELECT 'id1:value1,id3:value3' AS str FROM DUAL),
  split_data as (
    SELECT substr(regexp_substr(str, '[^,]+', 1, LEVEL),1,instr(regexp_substr(str, '[^,]+', 1, LEVEL), ':') - 1) as id,
           substr(regexp_substr(str, '[^,]+', 1, LEVEL),instr(regexp_substr(str, '[^,]+', 1, LEVEL), ':') + 1) as value
      FROM split_string
CONNECT BY INSTR (str, ',', 1, LEVEL - 1) > 0)
SELECT t.*
  FROM t_data t
  join split_data s
 on( t.id = s.id and t.value = s.value);

Upvotes: 1

Popeye
Popeye

Reputation: 35910

You can use the query using LIKE as follows:

SELECT *
  FROM T_DATA 
 WHERE ',' || YOUR_STRING || ',' LIKE '%,' || ID || ':' || VALUE || ',%'

Upvotes: 0

Related Questions