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

x
  • 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

Names
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
0
JCTDD
Asked:
JCTDD
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
0
 
Daniel_PLDB Expert/ArchitectCommented:
Or this:

--test
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

0
 
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

0
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.

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

Open in new window

0
 
Daniel_PLDB Expert/ArchitectCommented:
@kaminda
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 (,).
0
 
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
0
 
Ephraim WangoyaCommented:
try
0
 
Ephraim WangoyaCommented:
Sorry, here
select RIGHT(names, LEN(names) -CHARINDEX(',', names)) + ' ' +
       LEFT(names, CHARINDEX(',', names)-1)

Open in new window

0

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