Split String Oracle into a Single Column and Insert into a Table

Split string oracle into a single column and insert into a table

WITH data AS (
  SELECT 'test.doc#delimiter#1234,test1.doc#delimiter#1235,test2.doc#delimiter#1236' AS "value" FROM DUAL
  UNION ALL
  SELECT 'fil1.txt#delimiter#1456,fil1.txt#delimiter#1457' AS "value" FROM DUAL
)
SELECT  REGEXP_SUBSTR( data."value", '[^,]+', 1, levels.COLUMN_VALUE )
FROM    data,
        TABLE(
          CAST(
            MULTISET(
              SELECT LEVEL
              FROM   DUAL
              CONNECT BY  LEVEL <= LENGTH( regexp_replace( "value", '[^,]+'))  + 1
            ) AS sys.OdciNumberList
          )
        ) levels;

split string then insert records in a table

You can simply use one select statement where your string must be split into desired columns as following:

SQL> DROP TABLE EMP;

Table dropped.

SQL>
SQL> CREATE TABLE EMP (
  2      ID       NUMBER,
  3      NAME     VARCHAR2(10),
  4      SALARY   NUMBER,
  5      DEPT     VARCHAR2(10)
  6  );

Table created.

SQL>
SQL> DEFINE P_INPUT_STRING = '1$shubham$1000$comp#2$vijay$5000$civil#33$ram$23456$mech#';
SQL>
SQL> INSERT INTO EMP
  2      SELECT
  3          REGEXP_SUBSTR(INP, '[^$]+', 1, 1) AS ID,
  4          REGEXP_SUBSTR(INP, '[^$]+', 1, 2) AS NAME,
  5          REGEXP_SUBSTR(INP, '[^$]+', 1, 3) AS SALARY,
  6          REGEXP_SUBSTR(INP, '[^$]+', 1, 4) AS DEPT
  7      FROM
  8          (
  9              SELECT
 10                  REGEXP_SUBSTR(TRIM('#' FROM '&p_input_string'), '[^#]+', 1, LEVEL) AS INP
 11              FROM
 12                  DUAL
 13              CONNECT BY
 14                  LEVEL <= REGEXP_COUNT(TRIM('#' FROM '&p_input_string'), '#')
 15          );

2 rows created.

SQL>
SQL> SELECT * FROM EMP;

        ID NAME           SALARY DEPT
---------- ---------- ---------- ----------
         1 shubham          1000 comp
         2 vijay            5000 civil

SQL>

Cheers!!

split '$' seperated string and insert into table

Basically, you don't need PL/SQL for that.

Table:

SQL> create table test
  2    (emp_id number,
  3     emp_first_name varchar2(20),
  4     emp_last_name  varchar2(20),
  5     emp_salary number);

Table created.

Code:

SQL> insert into test (emp_id, emp_first_name, emp_last_name, emp_salary)
  2  with
  3  data (col) as
  4    (select '1$shubham$tathe$5000#2$vijaj$bakse$10000#3$ram$sharma$200' from dual),
  5  red as
  6    (select regexp_substr(col, '[^#]+', 1, level) val,
  7            level lvl
  8     from data
  9     connect by level <= regexp_count(col, '#') + 1
 10    ),
 11  emp as
 12    (select regexp_substr(val, '\w+', 1, 1) emp_id,
 13            regexp_substr(val, '\w+', 1, 2) emp_first_name,
 14            regexp_substr(val, '\w+', 1, 3) emp_last_name,
 15            regexp_substr(val, '\w+', 1, 4) emp_salary
 16     from red
 17    )
 18  select * From emp;

3 rows created.

Result:

SQL> select * From test;

    EMP_ID EMP_FIRST_NAME       EMP_LAST_NAME        EMP_SALARY
---------- -------------------- -------------------- ----------
         1 shubham              tathe                      5000
         2 vijaj                bakse                     10000
         3 ram                  sharma                      200

SQL>

If it has to be a procedure, no problem either.

Procedure:

SQL> rollback;

Rollback complete.

SQL> create or replace procedure p_test (par_col in varchar2) is
  2  begin
  3    insert into test (emp_id, emp_first_name, emp_last_name, emp_salary)
  4    with
  5    red as
  6      (select regexp_substr(par_col, '[^#]+', 1, level) val,
  7              level lvl
  8       from dual
  9       connect by level <= regexp_count(par_col, '#') + 1
 10      ),
 11    emp as
 12      (select regexp_substr(val, '\w+', 1, 1) emp_id,
 13              regexp_substr(val, '\w+', 1, 2) emp_first_name,
 14              regexp_substr(val, '\w+', 1, 3) emp_last_name,
 15              regexp_substr(val, '\w+', 1, 4) emp_salary
 16       from red
 17      )
 18    select * From emp;
 19  end;
 20  /

Procedure created.

Testing:

SQL> exec p_test('1$shubham$tathe$5000#2$vijaj$bakse$10000#3$ram$sharma$200');

PL/SQL procedure successfully completed.

SQL> select * From test;

    EMP_ID EMP_FIRST_NAME       EMP_LAST_NAME        EMP_SALARY
---------- -------------------- -------------------- ----------
         1 shubham              tathe                      5000
         2 vijaj                bakse                     10000
         3 ram                  sharma                      200

SQL>

Separate comma separated string into columns oracle sql

You can use REGEXP_SUBSTR, but you must specify the number of columns.

SELECT agentname
       ,REGEXP_SUBSTR (categories, '[^,]+', 1, 1)    AS CATA
       ,REGEXP_SUBSTR (categories, '[^,]+', 1, 2)    AS CATB  
       ,REGEXP_SUBSTR (categories, '[^,]+', 1, 3)    AS CATC 
       ,REGEXP_SUBSTR (categories, '[^,]+', 1, 4)    AS CATD
FROM commasplit;

demo in db<>fiddle

Oracle PL/SQL split string into columns, convert to numbers, and insert into new table

Have you tried TO_NUMBER function:

SELECT TO_NUMBER(REGEXP_SUBSTR(PtsString.column1, '[^( ,]+', 1, 1)) col_one,
       TO_NUMBER(REGEXP_SUBSTR(PtsString.column1, '[^ )]+', 1, 2)) col_two
FROM PuntosString;