Re: Oracle 10g SQL - Parsing Delimited String

A complete solution that will either find the last word or the last string as required would be:

SELECT sample

,SUBSTR( RTRIM(sample, ',')

,INSTR(RTRIM(sample, ',')

,',' ,-1) + 1)

AS LAST_STRING

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

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

,' ' ,-1) + 1) AS LAST_WORD

FROM

(SELECT 'Oracle 11g' AS sample FROM DUAL UNION ALL

SELECT ',,Oracle 9.2.1,,' AS sample FROM DUAL UNION ALL

SELECT ',,Oracle10,,' AS sample FROM DUAL UNION ALL

SELECT ',,,Database,,10g,,Oracle 10,,' AS sample FROM DUAL UNION ALL

SELECT ',,10g,,,Oracle20,,,,,,' AS sample FROM DUAL UNION ALL

SELECT ',,,Data,,,Menu,,,OracleAA,,,,' AS sample FROM DUAL

)

The result:

SAMPLE LAST_STRING LAST_WORD

----------------------------- ------------ -----------

1 Oracle 11g Oracle 11g 11g

2 ,,Oracle 9.2.1,, Oracle 9.2.1 9.2.1

3 ,,Oracle10,, Oracle10 Oracle10

4 ,,,Database,,10g,,Oracle 10,, Oracle 10 10

5 ,,10g,,,Oracle20,,,,,, Oracle20 Oracle20

6 ,,,Data,,,Menu,,,OracleAA,,,, OracleAA OracleAA

Stephen Miller - email: miller_stephen at usa.net

------ Original Message ------

Received: 01:41 PM EST, 01/11/2012

From: Michael Moore <[email protected]>

To: [email protected]

Cc: Vikram Vaidyanathan <[email protected]>

Subject: Re: [toad] Oracle 10g SQL - Parsing Delimited String

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 <[email protected] > 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

----------------------------- ---------

1 ,,Oracle10,, Oracle10

2 ,,,Database,,10g,,Oracle10,, Oracle10

3 ,,10g,,,Oracle20,,,,,, Oracle20

4 ,,,Data,,,Menu,,,OracleAA,,,, OracleAA

This replaces all of the commas with spaces, trims the result, and substrings out the last word in the resulting string - works even if there is only one string in the result.

Stephen Miller - email: miller_stephen at usa.net

------ Original Message ------

Received: 05:49 PM EST, 01/10/2012

From: Vikram Vaidyanathan <[email protected] >

To: "[email protected] " <[email protected] >

Subject: Re: [toad] Oracle 10g SQL - Parsing Delimited String

I am sorry if I wasn't very clear and precise.

The keyword in the example I gave i.e. Oracle can change and can be any string.

For example the column will contain values

1. ,,,Database,,10g,,Oracle10,,

2. ,,10g,,,Oracle20,,,,,,

3. ,,,Data,,,Menu,,,OracleAA,,,,

The output column should contain:

Oracle10

Oracle20

OracleAA

From: "Simoneau, Roger" <[email protected] >

To: "'[email protected] '" <[email protected] >

Sent: Tuesday, January 10, 2012 2:08 PM

Subject: RE: [toad] Oracle 10g SQL - Parsing Delimited String

>> Appreciate if you cna elaborate.

Instr <- oracle function that identifies the position of a value within another value, using a negative starting position indicates a reverse “look up”

So…

In the string “hello world, hello world, hello world”, entering:

Select instr(txt, ‘hello world’) from dual

Will respond with: 1 <- the position of the first occurrence

Select instr(txt, ‘hello world’, -1) from dual

Will respond with: 27 <- the third occurrence

Using substr from the position returned you can structure exactly what you want extracted.

Roger S.

This communication, including any attached documentation, is intended only for the person or entity to which it is addressed, and may contain confidential, personal and/or privileged information. Any unauthorized disclosure, copying, or taking action on the contents is strictly prohibited. If you have received this message in error, please contact us immediately so we may correct our records. Please then delete or destroy the original transmission and any subsequent reply.

__._,_.___

Your email settings: Individual Email|Traditional

Change settings via the Web (Yahoo! ID required)

Change settings via email: Switch delivery to Daily Digest | Switch to Fully Featured

Visit Your Group |
Yahoo! Groups Terms of Use |
Unsubscribe

__,_._,___