CODEWITHSUNDEEP
mysqlsql
David542
David542

Reputation: 110143

String replacement for formatting a name in mysql

I have a column called names that has inputs like the following:

`name`  
Aaron, James
Aberer, Leo
A.J.
A., Narayana Rao
Abbot Jr., Greg

I want to change the formatting such that the first name will come first, this is how it needs to be:

`name`
James Aaron
Leo Aberer
A.J.
Narayana Rao A.
Greg Abbot Jr.

How would I do this directly in mysql?

Upvotes: 0

Views: 84

Answers (2)

Zane Bien
Zane Bien

Reputation: 23125

You can use this solution:

SELECT 
    CASE WHEN INSTR(name, ',') > 0 
        THEN TRIM(CONCAT(SUBSTRING_INDEX(name, ',', -1), ' ', SUBSTRING_INDEX(name, ',', 1))) 
    ELSE name END AS name_formatted
FROM tbl

SQLFiddle Demo

Upvotes: 2

thomasrutter
thomasrutter

Reputation: 117333

If you can rely on the formatting in MySQL to always have exactly one comma in it, then you can use relatively simple MySQL functions.

SELECT CONCAT(SUBSTRING_INDEX(name, ',', -1), ' ', SUBSTRING_INDEX(name, ',' 1))
FROM names

But, your input also has one name that has no comma at all. In this case you need to check if it has a comma first. If you can rely on your input on always having either zero or one comma, you can use the following.

SELECT IF(
  LOCATE(name, ','),
  CONCAT(SUBSTRING_INDEX(name, ',', -1), ' ', SUBSTRING_INDEX(name, ',' 1)),
  name
) FROM names

Upvotes: 1

Related Questions