Oracle SQL Dynamic SUBSTRING

Question

I have a column (USERNAME) which contains client names. I need to be able to split this into two columns, FNAME and LNAME. The issue is that some people have double or even triple barrel forenames. The names in the USERNAME column are separated by spaces. For example as shown below, Surname is highlighted in bold

USERNAME

Stephen John Smith

Peter Jones

Brian James Andrew Spence

I have tried using regexp_substr, however this just gives me the the data up until the first space, so first name only

regexp_substr(USERNAME, '[^ ]+') AS FNAME

I also tried using SUBSTR and INSTR, which I could get the first Two names, which could also be used for three but it is not dynamic

substr(USERNAME, 1, instr(USERNAME || ' ', ' ', 2, 2) - 1) as FIRST_TWO_NAMES,

My thinking is that I need to work from the right, up until the last space, which will give me the Last name. Then use this statement again in another query to extract from the original column to give me the first name which can have multiple spaces in it. Or is there an easier solution to this issue?

Answer 1

You can use REGEXP_COUNT() function within the REGEXP_SUSBTR() to determine the occurence of the last space and the extracted surname, and then use REPLACE() in the next step to extract the name column :

WITH t AS
(
 SELECT REGEXP_SUBSTR( username, '[^ ]+',1,REGEXP_COUNT( username, '[^ ]+')) AS surname,
        username
   FROM tab 
) 
SELECT REPLACE(username,surname) AS name,
       surname
  FROM t

Demo

Answer 2

If you provide negative values for the third argument of instr, it works backwards from the end of the string:

with rws as (
  select 'Stephen John Smith' usr from dual union all
  select 'Peter Jones' usr from dual union all
  select 'Brian James Andrew Spence' usr from dual
)
  select substr ( usr, 1, instr ( usr, ' ', -1 ) - 1 ) firstname,
         substr ( usr, instr ( usr, ' ', -1 ) + 1 ) lastname
  from   rws;
  
FIRSTNAME             LASTNAME   
Stephen John          Smith       
Peter                 Jones       
Brian James Andrew    Spence