CODEWITHSUNDEEP
stringoracle-database
Drake
Drake

Reputation: 95

Spliting Fullname into Surname and Forename

I want to split the following name as Surname and Forename in Oracle. Please note that a full name can have 2, 3, 4 or more words. Last name is the surname. So basically I want the first word from the right as Surname and the remaining string on the left is the Forename.

Examples:

  1. "Mary Edward Alex Tario" - Here Surname should be "Tario" and Forename should be "Mary Edward Alex".
  2. "John Travolta" - Here Surname is "Travolta" and Forename is "John".

Upvotes: 1

Views: 72

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271131

Although you can do this with regexp_substr(), it is probably simpler with the basic string functions:

select substr(name, 1, length(name) - instr(reverse(name), ' ')) as firstname,
       substr(name, length(name) - instr(reverse(name), ' ') + 1) as lastname

Upvotes: 1

Related Questions