CODEWITHSUNDEEP
mysqlsql
O P
O P

Reputation: 2365

Reformat table data sql

I have a field name with the following rows:

`name`
------
John Smith
George Washington
Ash Ketchum
Bill O'Reilly

I would like to reformat the rows on output:

`name`
------
SMITH, JOHN
WASHINGTON, GEORGE
KETCHUM, ASH
O'REILLY BILL

I know I can use upper( x.name ) for casing, but how can I reformat the names?

Upvotes: 0

Views: 110

Answers (3)

echo_Me
echo_Me

Reputation: 37253

try this

   SELECT Concat(SUBSTRING_INDEX( `name` , ' ', -1 ) , ', ' , SUBSTRING_INDEX( `name` , ' ', 1 )) as name
 FROM your_Table

Upvotes: 1

Abe Miessler
Abe Miessler

Reputation: 85096

Try this:

SELECT SUBSTRING_INDEX( `name` , ' ', -1 ) + ', ' + SUBSTRING_INDEX( `name` , ' ', 1 )
FROM MyTable

@Ben, makes a good point about names with multiple spaces in them. I doubt this would give you the output you want in those cases, but it should get you started.

Upvotes: 1

gbtimmon
gbtimmon

Reputation: 4332

Assuming your rdbms is mySql, answer will need to be tailored to differenct dialects of sql. 

SELECT concat( substring(name, locate(name, ' ')), ', ',  substring(name, 0, locate(name, ' ') - 1)) FROM NAMES;

The real problem here however is data integrity. You need to have seperate columns for each peice of the name so that formating in the database is ensured to be consitent. Ideally you would want to be doing this --

Select concat(last_name,  ', ', first_name) FROM NAMES;

Allowing 

      'name' 
-------------------
John Smith
Smith, John
J Smith
Smith J
John q Smith
Jhon 'the smithmeister' Smith

All in the same column of a table is a bad thing for a number of reasons so this should be explictly prevented.

Upvotes: 1

Related Questions