split fullname into first name and last name in postgres

user_15319469 0
Viewed 559

im confused about function to separate fullname into first and last name. for example i have these records

      fullname
     ---------------
         john
        john doe
     john nick doe
  john smith nick doe

i expect result like this

firstname lastname
john john
john doe
john nick doe
john smith nick doe

can help me? thanks

postgresql
1 Answers

You could use REGEXP_REPLACE here:

SELECT
    fullname,
    REGEXP_REPLACE(fullname, '\s+\S+$', '') AS firstname,
    REGEXP_REPLACE(fullname, '^.*\s+(\S+)$', '\1') AS lastname
FROM yourTable;

screen capture from demo link below

Demo

Here is an explanation of the regex pattern used for the last name (the one used for the first name is a simplification of this):

^          from the start of the full name
    .*     match all content, greedily, until hitting
    \s+    the final space(s)
    (\S+)  then match and capture the final word in the full name (i.e. the last name)
$          end of the full name

Then, we replace with the captured last name, using \1.

user_18632290
Related