CODEWITHSUNDEEP
sqloracle-databaseplsql
Vish
Vish

Reputation: 11

Oracle PLSQL - How to insert pipeseparated string values into a table

Requirement is like:

I have a string with |(Pipe) separated. I need pull each string value and insert into a table.

eg: String is like A,B,C,D,E,F,L,R,X,Z

Table has all the columns from A to Z

If the given string has A value, the column should be inserted with 'Yes' else 'No'. Like this if string has value that corresponding column in the table should become 'Yes' else as 'No'.

I wrote a procedure where it is inserting in different rows. 

DECLARE 
LV_VUSR VARCHAR2(1000);
BEGIN
FOR J IN (SELECT COLUMN_VALUE FROM TABLE(MS_APPS_UTILITIES.SPLIT_STRING('A,B,D',','))) 
LOOP
  LV_VUSR := J.COLUMN_VALUE;

     INSert into dummy_v 
     values ('1', decode(J.column_value,'A','Y','N'),decode(J.column_value,'B','Y','N'),decode(J.column_value,'C','Y','N'),decode(J.column_value,'D','Y','N'));

END LOOP;
END;

I have written this block where multiple rows are getting inserted for the same id. Can any one help on this?

Upvotes: 0

Views: 1981

Answers (2)

Matthew McPeak
Matthew McPeak

Reputation: 17924

This might be overly simplified, but if you can rely on the quality of your input data, why not just this:

INSERT INTO table_name
WITH data ( id, value ) AS (
  SELECT 1, 'A,B,E' FROM DUAL UNION ALL
  SELECT 2, 'B,E' FROM DUAL
)
SELECT d.id,
       CASE WHEN INSTR(d.value,'A') > 0 THEN 'Y' ELSE 'N' END A,
       CASE WHEN INSTR(d.value,'B') > 0 THEN 'Y' ELSE 'N' END B,
       CASE WHEN INSTR(d.value,'C') > 0 THEN 'Y' ELSE 'N' END C,
       CASE WHEN INSTR(d.value,'D') > 0 THEN 'Y' ELSE 'N' END D,
       CASE WHEN INSTR(d.value,'E') > 0 THEN 'Y' ELSE 'N' END E
FROM   data d

REGEXP_INSTR could be used instead to cover some minor variations in the input data.

Upvotes: 2

MT0
MT0

Reputation: 167867

A smaller example with 5 columns (you should be able to scale it up to all 26):

Oracle Setup:

CREATE TABLE table_name (
  id INT,
  A CHAR(1),
  B CHAR(1),
  C CHAR(1),
  D CHAR(1),
  E CHAR(1)
);

Query:

INSERT INTO table_name
WITH data ( id, value ) AS (
  SELECT 1, 'A,B,E' FROM DUAL UNION ALL
  SELECT 2, 'B,E' FROM DUAL
)
SELECT d.id, 
       CASE WHEN COUNT( CASE WHEN t.COLUMN_VALUE = 'A' THEN 1 END ) > 0
            THEN 'Y' ELSE 'N' END, 
       CASE WHEN COUNT( CASE WHEN t.COLUMN_VALUE = 'B' THEN 1 END ) > 0
            THEN 'Y' ELSE 'N' END, 
       CASE WHEN COUNT( CASE WHEN t.COLUMN_VALUE = 'C' THEN 1 END ) > 0
            THEN 'Y' ELSE 'N' END, 
       CASE WHEN COUNT( CASE WHEN t.COLUMN_VALUE = 'D' THEN 1 END ) > 0
            THEN 'Y' ELSE 'N' END, 
       CASE WHEN COUNT( CASE WHEN t.COLUMN_VALUE = 'E' THEN 1 END ) > 0
            THEN 'Y' ELSE 'N' END
FROM   data d,
       TABLE(
        CAST(
          MULTISET(
            SELECT REGEXP_SUBSTR( d.value, '[^,]+', 1, LEVEL )
            FROM   DUAL
            CONNECT BY LEVEL <= REGEXP_COUNT( d.value, '[^,]+' )
          ) AS SYS.ODCIVARCHAR2LIST
        )
       ) t
GROUP BY d.id;

Output:

SELECT * FROM table_name;

        ID A B C D E
---------- - - - - -
         1 Y Y N N Y 
         2 N Y N N Y

Or a solution using a short stored function:

Oracle Setup:

CREATE TYPE stringlist IS TABLE OF VARCHAR2(20);
/

CREATE FUNCTION split_String(
  i_str    IN  VARCHAR2,
  i_delim  IN  VARCHAR2 DEFAULT ','
) RETURN stringlist DETERMINISTIC
AS
  p_result       stringlist := stringlist();
  p_start        NUMBER(5) := 1;
  p_end          NUMBER(5);
  c_len CONSTANT NUMBER(5) := LENGTH( i_str );
  c_ld  CONSTANT NUMBER(5) := LENGTH( i_delim );
BEGIN
  IF c_len > 0 THEN
    p_end := INSTR( i_str, i_delim, p_start );
    WHILE p_end > 0 LOOP
      p_result.EXTEND;
      p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, p_end - p_start );
      p_start := p_end + c_ld;
      p_end := INSTR( i_str, i_delim, p_start );
    END LOOP;
    IF p_start <= c_len + 1 THEN
      p_result.EXTEND;
      p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, c_len - p_start + 1 );
    END IF;
  END IF;
  RETURN p_result;
END;
/

Query:

INSERT INTO table_name
WITH data ( id, value ) AS (
  SELECT 1, 'A,B,E' FROM DUAL UNION ALL
  SELECT 2, 'B,E' FROM DUAL
)
SELECT id,
       CASE WHEN 'A' MEMBER OF vs THEN 'Y' ELSE 'N' END,
       CASE WHEN 'B' MEMBER OF vs THEN 'Y' ELSE 'N' END,
       CASE WHEN 'C' MEMBER OF vs THEN 'Y' ELSE 'N' END,
       CASE WHEN 'D' MEMBER OF vs THEN 'Y' ELSE 'N' END,
       CASE WHEN 'E' MEMBER OF vs THEN 'Y' ELSE 'N' END
FROM  (
  SELECT id,
         split_String( value ) AS vs
  FROM   data
);

Output:

SELECT * FROM table_name;

        ID A B C D E
---------- - - - - -
         1 Y Y N N Y 
         2 N Y N N Y

Upvotes: 1

Related Questions