1

I have a person table where the name column contains names, some in the format "first last" and some in the format "first".

My query

SELECT name,
SUBSTRING(name FROM 1 FOR POSITION(' ' IN name) ) AS first_name
FROM person

creates a new row of first names, but it doesn't work for the names which only have a first name and no blank space at all.

I know I need a CASE statement with something like 0 = (' ', name) but I keep running into syntax errors and would appreciate some pointers.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Jelly legs mick
  • 33
  • 1
  • 5

3 Answers3

5

Just use split_part():

SELECT split_part(name, ' ', 1) AS first_name
     , split_part(name, ' ', 2) AS last_name
FROM person;

SQL Fiddle.

Related:

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
0

1.

select 
 substring(name from '^([\w\-]+)') first_name,
 substring(name from '\s(\w+)$') last_name
from person

2.

select (regexp_split_to_array(name, ' '))[1] first_name
     , (regexp_split_to_array(name, ' '))[2] last_name
from person
DanStopka
  • 535
  • 5
  • 22
0

Both Erwin and Danstopka have the best answer in their own style. I have used their approach to extend to my use case where we wanted middle name only if it exists.

--Example

WITH tablename( name )      AS 
     ( 
            SELECT 'Barn KE Roman' 
            UNION ALL 
            SELECT 'Utry Rita' 
            UNION ALL 
            SELECT 'Jam Ilya Dimo' 
     ) 
SELECT (Regexp_split_to_array(NAME, ' '))[1] first_name , ( 
       CASE 
              WHEN array_length(regexp_split_to_array(NAME, ' '),1) > 2 THEN (regexp_split_to_array(NAME, ' '))[2] 
       END ) middle_name , ( 
       CASE 
              WHEN array_length(regexp_split_to_array(NAME, ' '),1) > 2 THEN (regexp_split_to_array(NAME, ' '))[3] 
              ELSE (regexp_split_to_array(NAME, ' '))[2] 
       END ) last_name 
FROM   tablename

Nothing special, I take the length of each array and check if the it has more than 3 words and then decide the middle and last name accordingly.

Cloudata
  • 1