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?

And what about last names in multiple parts? You really cannot distinguish between first and last names in your column. — Gordon Linoff
– Gordon Linoff, Commented Aug 11, 2020 at 13:28

Chris Saxon · Accepted Answer · 2020-08-11 13:22:47Z

3

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

edited Aug 11, 2020 at 13:22

answered Aug 11, 2020 at 13:09

Chris Saxon

10.2k1 gold badge33 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

scubbastevie Over a year ago

Great thank you, then if I want to use this query to subtract the surname from the original column, to give me firstname, can I subtract it?

Chris Saxon Over a year ago

Use it to go from position 1 to the location of the last space, example updated

Barbaros Özhan · Accepted Answer · 2020-08-11 13:23:37Z

0

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

answered Aug 11, 2020 at 13:23

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Collectives™ on Stack Overflow

Oracle SQL Dynamic SUBSTRING

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related