Oracle- Split string comma delimited (string contains spaces and consecutive commas)

by

Try this for the parsing the list part. It handles NULLS:

SQL> select regexp_substr('12 3,456,,abc,def', '(.*?)(,|$)', 1, level, null, 1) SPLIT, level
    from dual
    connect by level <= regexp_count('12 3,456,,abc,def',',') + 1
    ORDER BY level;

SPLIT                  LEVEL
----------------- ----------
12 3                       1
456                        2
                           3
abc                        4
def                        5

SQL>

Unfortunately when you search for regex’s for parsing lists, you will always find this form which does NOT handle nulls and should be avoided: '[^,]+'. See here for more info: Split comma separated values to columns in Oracle.

More Related Contents:

  1. Receive an unexpected symbol error in my PLSQL query [closed]
  2. PL SQL UPDATE STATEMENT
  3. Search All Fields In All Tables For A Specific Value (Oracle)
  4. Is there a combination of “LIKE” and “IN” in SQL?
  5. Is it possible to output a SELECT statement from a PL/SQL block?
  6. REGEX to select nth value from a list, allowing for nulls
  7. SQL to generate a list of numbers from 1 to 100
  8. How to use BOOLEAN type in SELECT statement
  9. create table with sequence.nextval in oracle [duplicate]
  10. IF EXISTS condition not working with PLSQL
  11. passing table and column name dynamically using bind variables
  12. WHERE IN condition not accepting String value
  13. Split String by delimiter position using oracle SQL
  14. Is there any way to flush output from PL/SQL in Oracle?
  15. Sleep function in ORACLE
  16. Solution to “cannot perform a DML operation inside a query”?
  17. Array in IN() clause oracle PLSQL
  18. Split comma separated values of a column in row, through Oracle SQL query
  19. PLSQL Insert into with subquery and returning clause
  20. oracle — Split multiple comma separated values in oracle table to multiple rows
  21. What does the colon sign “:” do in a SQL query?
  22. How to query a CLOB column in Oracle
  23. Oracle (Old?) Joins – A tool/script for conversion?
  24. Advice Using Pivot Table in Oracle
  25. Creating or simulating two dimensional arrays in PL/SQL
  26. How to check if a column exists before adding it to an existing table in PL/SQL?
  27. Subtracting Dates in Oracle – Number or Interval Datatype?
  28. How to call Oracle MD5 hash function?
  29. ORACLE After update trigger: solving ORA-04091 mutating table error
  30. Using bind variables with dynamic SELECT INTO clause in PL/SQL

Leave a Comment