REGEX to select nth value from a list, allowing for nulls

by

Thanks to those who replied. After perusing your answers and the answers in the link supplied, I arrived at this solution:

SQL> select REGEXP_SUBSTR('1,,3,4,5', '(.*?)(,|$)', 1, 2, NULL, 1) data
  2  from dual;

Data
----

Which can be described as “look at the 2nd occurrence of an optional set of zero or more characters that are followed by a comma or the end of the line, and return the 1st subgroup (which is the data less the comma or end of the line).

I forgot to mention I tested with the null in various positions, multiple nulls, selecting various positions, etc.

The only caveat I could find is if the field you look for is greater than the number available, it just returns NULL so you need to be aware of that. Not a problem for my case.

EDIT: I am updating the accepted answer for the benefit of future searchers that may stumble upon this.

The next step is to encapsulate the code so it can be made into a simpler, reusable function. Here is the function source:

  FUNCTION  GET_LIST_ELEMENT(string_in VARCHAR2, element_in NUMBER, delimiter_in VARCHAR2 DEFAULT ',') RETURN VARCHAR2 IS
    BEGIN
      RETURN REGEXP_SUBSTR(string_in, '(.*?)(\'||delimiter_in||'|$)', 1, element_in, NULL, 1);
  END GET_LIST_ELEMENT;

This hides the regex complexities from developers who may not be so comfortable with it and makes the code cleaner anyway when in use. Call it like this to get the 4th element:

select get_list_element('123,222,,432,555', 4) from dual;

More Related Contents:

  1. PLS-00428: an INTO clause is expected in this SELECT statement
  2. Sleep function in ORACLE
  3. Oracle 11g – Check constraint with RegEx
  4. Issue when comparing result of to_char(myDate, ‘DAY’) to a string
  5. Subtracting Dates in Oracle – Number or Interval Datatype?
  6. How to call Oracle MD5 hash function?
  7. Using bind variables with dynamic SELECT INTO clause in PL/SQL
  8. Receive an unexpected symbol error in my PLSQL query [closed]
  9. PL SQL UPDATE STATEMENT
  10. Search All Fields In All Tables For A Specific Value (Oracle)
  11. Is it possible to output a SELECT statement from a PL/SQL block?
  12. SQL to generate a list of numbers from 1 to 100
  13. Oracle Joins – Comparison between conventional syntax VS ANSI Syntax
  14. How to use BOOLEAN type in SELECT statement
  15. SQL recursive query on self referencing table (Oracle)
  16. create table with sequence.nextval in oracle [duplicate]
  17. IF EXISTS condition not working with PLSQL
  18. Using pivot on multiple columns of an Oracle row
  19. WHERE IN condition not accepting String value
  20. Pivoting of data using two columns
  21. INSERT SELECT statement in Oracle 11G
  22. Solution to “cannot perform a DML operation inside a query”?
  23. oracle — Split multiple comma separated values in oracle table to multiple rows
  24. How to query a CLOB column in Oracle
  25. Oracle (Old?) Joins – A tool/script for conversion?
  26. Display names of all constraints for a table in Oracle SQL
  27. How to use a dynamic parameter in a IN clause of a JPA named query?
  28. Creating or simulating two dimensional arrays in PL/SQL
  29. Oracle string aggregation
  30. Oracle RAC and sequences

Leave a Comment