Re: Oracle 10g SQL - Parsing Delimited String

Toad for Oracle & DevOps Toolkit
1

The following should work in all current versions of ORACLE:

SELECT sample
,SUBSTR( TRIM(REPLACE(sample ,’,’ ,’ ‘))
,INSTR(TRIM(REPLACE(sample ,’,’ ,’ ‘))
,’ ’ ,-1) + 1) AS extracted
FROM
(SELECT ‘,Oracle10,’ AS sample FROM DUAL UNION ALL
SELECT ‘,Database,10g,Oracle10,’ AS sample FROM DUAL UNION ALL
SELECT ‘,10g,Oracle20,’ AS sample FROM DUAL UNION ALL
SELECT ‘,Data,Menu,OracleAA,’ AS sample FROM DUAL
)

SAMPLE EXTRACTED

2

Very nice, Stephen!

If the target string might include blanks, your code will extract the portion
after the last blank. In that case, this variation might work better:

SUBSTR( RTRIM(sample , ‘,’ )

,INSTR(RTRIM(sample , ‘,’ ), ‘,’ ,- 1 )

  • 1 ) AS extracted

Nate Schroeder

Enterprise Services - Data Management Team

Monsanto Company

800 N. Lindbergh Blvd. LC4D - Saint Louis, MO - 63167

314-694-2592

3

SELECT sample

      ,SUBSTR(      TRIM(REPLACE(sample ,',' ,'

'))

             ,INSTR(TRIM(REPLACE(sample ,',' ,'

'))

             ,' ' ,-1) + 1)                 AS

extracted

  FROM

(SELECT ‘,Test This,’ AS sample FROM
DUAL

);

So, if you have embedded spaces, Stephen’s solution will give the wrong
result.

Mike

On Wed, Jan 11, 2012 at 10:02 AM, Stephen Miller
wrote:

The following should work in all current versions of ORACLE:

    SELECT sample
          ,SUBSTR(      TRIM(REPLACE(sample ,','
,' '))
                 ,INSTR(TRIM(REPLACE(sample ,','
,' '))
                 ,' ' ,-1) +
1)                 AS extracted
      FROM
   (SELECT ',,Oracle10,,'                  AS
sample FROM DUAL UNION ALL
    SELECT ',,,Database,,10g,,Oracle10,,'  AS sample FROM DUAL
UNION ALL
    SELECT ',,10g,,,Oracle20,,,,,,'        AS sample FROM
DUAL UNION ALL
    SELECT ',,,Data,,,Menu,,,OracleAA,,,,' AS sample FROM DUAL
   )

  SAMPLE                        EXTRACTED