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