[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 505
  • Last Modified:

SQL change Surname, Firstname to Firstname Surname with no comma

I have a table called 'Names' displaying like this

Smith, John
Jane, Mary
Chong, Bob

can you please tell me the SQL code (starting with 'select') in order to display it like this:
John Smith
Mary Jane
Bob Chong
1 Solution
Pratima PharandeCommented:
try this

select Ltrim(rTrim(SUBSTRING(Names, CHARINDEX(',', Names) +1 ,Len(Names)))) + ' ' + Ltrim(rTrim(SUBSTRING(Names, 1, CHARINDEX(',', Names) -1))) from table_name
Daniel_PLDB Expert/ArchitectCommented:
Or this:

declare @Names varchar(100)
set @Names='Chong, Bob'
SELECT SUBSTRING(@Names,PATINDEX('%[,]%',@Names)+1,PATINDEX('%[,]%',@Names)+1)+ ' ' + SUBSTRING(@Names,0,PATINDEX('%[,]%',@Names))

--select from table
SELECT SUBSTRING(Names,PATINDEX('%[,]%',Names)+1,PATINDEX('%[,]%',Names)+1)+ ' ' + SUBSTRING(Names,0,PATINDEX('%[,]%',Names)) FROM table_name;

Open in new window

Rikin ShahMicrosoft Dynamics CRM ConsultantCommented:
select ltrim(substring (Names, CHARindex(',',Names)+1, len(Names))) + ' ' + left(Names, CHARindex(',',Names)-1) 
from table

Open in new window

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

I would do it like this
SELECT REPLACE(Names,',','') as Names FROM tableName

Open in new window

Daniel_PLDB Expert/ArchitectCommented:
Yes, it could be done just by REPLACE but read question more carefully, asker wants to have Surname and Firstname switched. You are only removing a comma (,).
Alpesh PatelAssistant ConsultantCommented:
select Ltrim(rTrim(SUBSTRING('Alpesh, Patel', CHARINDEX(',', 'Alpesh, Patel') +1 ,Len('Alpesh, Patel')))) + ' ' + Ltrim(rTrim(SUBSTRING('Alpesh, Patel', 1, CHARINDEX(',', 'Alpesh, Patel') -1)))
from table_name

TRy this
Ephraim WangoyaCommented:
Ephraim WangoyaCommented:
Sorry, here
select RIGHT(names, LEN(names) -CHARINDEX(',', names)) + ' ' +
       LEFT(names, CHARINDEX(',', names)-1)

Open in new window


Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Tackle projects and never again get stuck behind a technical roadblock.
Join Now