Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Chopping string into text and numbers using SUBSTR and INSTR

969483Dec 23 2014 — edited Dec 23 2014

I have the following sample data. I know it's terrible database design to have a combination of text and numbers in the same field, but I can't change the business process.

This is what I've got so far:

set linesize 5000

WITH table_data

     AS (SELECT 'R: JASON BOURNE 12341560' str FROM DUAL UNION ALL

         SELECT 'R: FATS DOMINO 23432342' str FROM DUAL UNION ALL

         SELECT 'R: MARTIN LUTHER KING 3334156' str FROM DUAL UNION ALL

         SELECT 'R: TOM CRUISE 6547123' str FROM DUAL UNION ALL

         SELECT 'R: TOMMY DE GROOT 1212124' str FROM DUAL UNION ALL

         SELECT 'R: GRIM REAPER 1345245' str FROM DUAL UNION ALL

         SELECT 'R: DOCTOR DEATH THE FIRST SMITH 24356178' str FROM DUAL)

SELECT str

     , TRIM(SUBSTR(str, INSTR(str, ' ', 1, 1), INSTR (str, ' ', 1, 2) - INSTR (str, ' ', 1, 1))) first_name

     , REPLACE(SUBSTR(str, INSTR (str, ' ', 1, 2)+1, 999), SUBSTR(str,LENGTH(str)-6,7), '') last_name

     , SUBSTR(str,LENGTH(str)-6,7) emp_num

  FROM table_data;

STR                                      FIRST_NAME    LAST_NAME                 EMP_NUM

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

R: JASON BOURNE 12341560                 JASON         BOURNE 1                  2341560

R: FATS DOMINO 23432342                  FATS          DOMINO 2                  3432342

R: MARTIN LUTHER KING 3334156            MARTIN        LUTHER KING               3334156

R: TOM CRUISE 6547123                    TOM           CRUISE                    6547123

R: TOMMY DE GROOT 1212124                TOMMY         DE GROOT                  1212124

R: GRIM REAPER 1345245                   GRIM          REAPER                    1345245

R: DOCTOR DEATH THE FIRST SMITH 24356178 DOCTOR        DEATH THE FIRST SMITH 2   4356178

7 rows selected.

I need to:

1. Extract the first name - which is always the first name after the "R: " - I've got that bit working fine.

2. Extract the last name - should be the last word before the number, so e.g. for the last night, I'd only want "SMITH", and "KING" for the 3rd line, and "GROOT" for the 5th line.

3. Extract the number at the end, regardless of length - some of the examples above are 8 numbers long so my attempt above isn't working.

I cannot use REGEXP_SUBSTR or any REGEXP as they're not registered in the EUL I'm using in Discoverer, I can only use INSTR AND SUBSTR.

I wondered if anyone might be able to help please?

Any advice much appreciated.

Thanks

This post has been answered by BluShadow on Dec 23 2014
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 20 2015
Added on Dec 23 2014
6 comments
315 views