CODEWITHSUNDEEP
sqlpostgresqlsubstring
Paul Taylor
Paul Taylor

Reputation: 13110

How do I convert table column data from 'LastName, FirstName' to 'FirstName LastName' in Postgres

How do I convert table column data from 'LastName, FirstName' to 'FirstName LastName' in Postgres.

So if the table column contains 'Ona, Lisa' I want it to return 'Lisa Ona'. If the column doesnt contain a ' ,' then I want it to return null. I've been trying to use postgres functions SUBSTRING and REGEXP_REPLACE but cannot get anything working.

Upvotes: 1

Views: 195

Answers (3)

Paul Taylor
Paul Taylor

Reputation: 13110

Nevermind worked out a solution using SPLIT_PART function

SELECT 
t1.sort_name,
split_part(t1.sort_name, ', ', 2)|| ' ' || split_part(t1.sort_name, ', ', 1)  
FROM artist t1
WHERE 
t1.sort_name LIKE '%, %'

Upvotes: 1

Vivek S.
Vivek S.

Reputation: 21915

try this

select split_part(name,',',2) ||' '|| split_part(name,',',1) as "thename" from tblname     
where name LIKE '%,%'

String Functions and Operators and PostgreSQL SPLIT PART

Upvotes: 0

Jakub Kania
Jakub Kania

Reputation: 16487

You need strpos and substr functions and a CASE to return NULL.

SELECT CASE WHEN strpos(name,', ') > 0 
            THEN substr(name,strpos(name,', ') + 2)  ||' ' || substr(name,0, strpos(name,', '))
            ELSE NULL END
    FROM person

fiddle

Upvotes: 2

Related Questions