Need to fetch a particular substring from a delimited string in sql

Question

I have a string in SQL as

1|208080|"20170815|00:00:00"|"20160615|23:59:59

I just need to fetch the value 20160615 from the select statement in SQL

is it always after the second last `|`? Also which DBMS you are using? — Nebi, Sep 15 '16 at 12:20
Really bad idea to store data like that... Hope you're trying to fix it now! — jarlh, Sep 15 '16 at 12:23
@Nebi : Ya it is always after 2nd last, Software being used is Oracle SQL developer — Amar Kumar, Sep 15 '16 at 12:30
Do you have a typo in the unbalanced double-quotes? Also what have you tried so far? — Gary_W, Sep 15 '16 at 13:37

score 0 · Answer 1 · answered Sep 15 '16 at 13:51

This regular expression handles NULL elements should they exist in the list and grabs only the digits in the 5th pipe-delimited field (the double-quote looks like a typo but you never know). It's flexible so you can get any field you want, just replace the '5' in the call with the field you want.

SQL> with tbl(str) as (
     select '1|208080|"20170815|00:00:00"|"20160615|23:59:59'
     from dual
   )
   select regexp_substr(str, '(\d*?)(\||$)', 1, 5, NULL, 1) field5
   from tbl;

FIELD5
--------
20160615

SQL>

Need to fetch a particular substring from a delimited string in sql

1 Answers1