CODEWITHSUNDEEP
sql-server
ntep vodka
ntep vodka

Reputation: 735

sql server split name

i just want to ask how to split name using SQL Server 2008. i found this code Split Name

here's the code 

 SELECT SUBSTRING(Leadname, 1, NULLIF(CHARINDEX(' ', Leadname) - 1, -1)) AS [FirstName],
 SUBSTRING(Leadname, CHARINDEX(' ', Leadname) + 1, LEN(Leadname)) AS [LastName]
 FROM Customer

lets say that the data is "John Doe", and using that query, the output like this : 

   First Name    Last Name
   John          Doe

but in my Customer Table, i have Leadname only one word like John. Using that query, i get the result like this : 

   First Name   Last Name
   NULL         John

all i want, if i only have one word in my Leadname, the result is 

   First Name   Last Name
   John         NULL

what should i do to make it ?

thanks

Upvotes: 1

Views: 1937

Answers (2)

Aaron Mendoza
Aaron Mendoza

Reputation: 1

If you need to split a FullName into existing rows, you can use the update statement below.

update Customer    
set FirstName = SUBSTRING([FullName],1,CHARINDEX(' ',[FullName]))
set LastName = SUBSTRING([FullName],CHARINDEX(' ',[FullName]),LEN([FullName]))

Upvotes: 0

Sam Saffron
Sam Saffron

Reputation: 131092

This works: 

SELECT 
 SUBSTRING(Name, 1, case when CHARINDEX(' ', Name) = 0 then LEN(Name) else CHARINDEX(' ', Name) end) AS [FirstName],
 case when CHARINDEX(' ', Name) = 0 then null else  SUBSTRING(Name, CHARINDEX(' ', Name) + 1, LEN(Name)) end
 AS [LastName]
 FROM (
    select 'jon doe' Name
    union 
    select 'jon'
 ) X

Results: 

FirstName LastName
--------- --------
jon       doe
jon       NULL

(2 row(s) affected)

You could do it a bit cleaner albeit slightly less efficiently with a user defined function

Upvotes: 1

Related Questions