CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Emman Bangis
Emman Bangis

Reputation: 85

How do I split and comma-delimit names?

I have this column

NAME
John Stephenson
James Martin
Anna Corelia

How I can select this column to this?

NAME
Stephenson, John
Martin, James
Corelia, Anna

Upvotes: 0

Views: 84

Answers (5)

Anon
Anon

Reputation: 10908

SELECT substring(NAME+', '+NAME,charindex(' ',NAME)+1,len(NAME)+1) FROM MyTable

This also turns "Rip Van Winkle" into "Van Winkle, Rip"

SQL Fiddle Demo

Upvotes: 0

Dave C
Dave C

Reputation: 7392

Here is a solution including how I got the final output column.

This will handle three part names, like "Rip Van Winkle", also names with no white space and will strip (trim) any leading/trailing white space from the names.

DECLARE @NAMES TABLE (NAME NVARCHAR(MAX))
INSERT INTO @NAMES (NAME) VALUES ('John Stephenson'),('James Martin'),('Anna Corelia'),('Rip Van Winkle'),('Sally')

SELECT *, 
       CHARINDEX(' ',LTRIM(RTRIM(NAME))) [SpaceLocation],
       CASE WHEN CHARINDEX(' ',LTRIM(RTRIM(NAME)))>0 THEN RIGHT(LTRIM(RTRIM(NAME)), LEN(LTRIM(RTRIM(NAME)))-CHARINDEX(' ',LTRIM(RTRIM(NAME)))) END [LastName],
       CASE WHEN CHARINDEX(' ',LTRIM(RTRIM(NAME)))>0 THEN LEFT(LTRIM(RTRIM(NAME)),CHARINDEX(' ',LTRIM(RTRIM(LTRIM(RTRIM(NAME)))))-1) END [FirstName],
       CASE WHEN CHARINDEX(' ',LTRIM(RTRIM(NAME)))>0 THEN RIGHT(LTRIM(RTRIM(NAME)), LEN(LTRIM(RTRIM(NAME)))-CHARINDEX(' ',LTRIM(RTRIM(NAME)))) + ', ' +LEFT(LTRIM(RTRIM(NAME)),CHARINDEX(' ',LTRIM(RTRIM(NAME)))-1) ELSE LTRIM(RTRIM(NAME)) END [FinalOutput]
FROM @NAMES

Upvotes: 0

Quijote Shin
Quijote Shin

Reputation: 501

There is no a single trim function but you can 

 select   ltrim(rtrim(colname)) from tablename

which will TRIM both sides.

but what you need is:

declare @name varchar(300) ='Stephenson John';
Select  

Substring(@name, 1,Charindex(' ', @name)-1) +
', '  +
Substring(@name, Charindex(' ', @name)+1, LEN(@name))
 as name

Sorry I miss post

Upvotes: -1

Alex K.
Alex K.

Reputation: 175766

One way

;with test(name) as (
    select 'John Stephenson' union all
    select 'James Martin' union all
    select 'Anna J. Corelia' union all
    select 'BOBBYTABLES'
)
select
case when charindex(' ', name) = 0 then name  
else right(name, charindex(' ', reverse(name)) - 1) + ', ' + substring(name, 1, len(name) - charindex(' ', reverse(name))) end
from test


(No column name)
Stephenson, John
Martin, James
Corelia, Anna J.
BOBBYTABLES

Upvotes: 1

Rahul
Rahul

Reputation: 77866

Your question has nothing to do with TRIM() function. Probably you are trying to get something like below using LEFT() and RIGHT() function of SQL Server and concatanating them with ,

select right('John Stephenson',(len('John Stephenson')-charindex(' ','John Stephenson')))
+ ', ' + left('John Stephenson',(charindex(' ','John Stephenson') - 1))

which will result in 

Stephenson, John

Upvotes: 1

Related Questions