SQL switch firstname and lastname-CodePudding

I am Sorry for question but I am stuck. How can I switch row where is lastname and first name = 'Ware, John' to get fullname without comma 'John Ware' ? Thank you very much

SELECT
    SUBSTR( NAME, 1, ( LOCATE( ',', NAME ) ) ) AS FIRSTTNAME,
    SUBSTR( NAME,    ( LOCATE( ',', NAME ) ) ) AS LASTNAME
FROM
    table

I am keep getting error:

No function matches the given name and argument types. You might need to add explicit type casts.

Names
Camerata Si, The
Zukerman, Pinchas
Phelps, Cynthia
Watts, Andre (André)

CodePudding user response：

The REGEXP_REPLACE function offers one convenient approach here:

SELECT full_name,
       REGEXP_REPLACE(full_name, '([^,] ),\s*(.*)', '\2 \1') AS full_name_out
FROM yourTable;

Output:

full_name            | full_name_out
Camerata Si, The     | The Camerata Si
Zukerman, Pinchas    | Pinchas Zukerman
Phelps, Cynthia      | Cynthia Phelps
Watts, Andre (André) | Andre (André) Watts

Here is a running demo for Postgres.

CodePudding user response：

You still need to check for conditions where comma does not exist, etc, but in a crude way:

select 
trim(substring(name, position(',' in name) 1)) as FIRSTNAME,
trim(substring(name, 1, position(',' in name)-1)) as LASTNAME 
from Tbl