6

I'm trying to split a string in MSSQL by only the first whitespace Considering here can have 2 spaces in their full name, I have no idea how to do this.

Example:

Henk de Vries

I would like to split it into:

Firstname: Henk
Lastname: de Vries
codingbadger
  • 42,678
  • 13
  • 95
  • 110
user1213904
  • 1,830
  • 4
  • 23
  • 39
  • http://stackoverflow.com/questions/5928599/equivalent-of-explode-to-work-with-strings-in-mysql – Hanky Panky Jun 13 '14 at 06:36
  • This link might be helpful to you. http://stackoverflow.com/questions/14950466/how-to-split-the-name-string-in-mysql – Charvee Shah Jun 13 '14 at 06:39
  • Ok i've looked into the links. Do you guys know if SUBSTRING_INDEX is any comparable to CHARINDEX in mssql? – user1213904 Jun 13 '14 at 06:40
  • 1
    the links refering to duplicates concerns mysql. This is not a mysql question. Although the question HAS been asked before in sqlserver. – t-clausen.dk Jun 13 '14 at 09:11

4 Answers4

8

try using Patindex

create table #t(name varchar(20))
insert into #t values('Henk de Vries')

select substring(name,1,PATINDEX('% %',name)) as First_name,SUBSTRING(name,patindex('% %',name),LEN(name)) as Last_Name from #t

This is done to fix as said in comments by t-clausen.dk

select left(name,charindex(' ',name+' ')) as First_Name,substring(name,charindex(' ',name+' '),len(name)) as Last_Name from #t

An demo to test with

Updated demo

vhadalgi
  • 7,027
  • 6
  • 38
  • 67
  • 1
    Hey thanks, I just noticed I had to use a Derrived Column (Visual studio ssis) And got the following with the help of your answer and google :) : SUBSTRING(Name,FINDSTRING(Name," ",1) + 1,LEN(Name) - FINDSTRING(Name," ",1)) Thanks a lot – user1213904 Jun 13 '14 at 07:02
  • This is not the best way to handle splitting. You are assuming the data will always include a space. You can use left, stuff and charindex with better result instead – t-clausen.dk Jun 13 '14 at 09:13
  • 1
    @t-clausen.dk : yes +1! i didn't see that coming as OP mentioned this _Considering here can have 2 spaces in their full name_ but yes u're right! – vhadalgi Jun 13 '14 at 09:21
4

Here is an example that will compare this answer to the chosen answer, note that the chosen answer has bugs when there is just 1 name (I know most people have combined names, but this is not a perfect world):

SELECT 
  LEFT(name, charindex(char(32), name + char(32))) Firstname,
  STUFF(name, 1, charindex(char(32), name), '') LastName,
  -- included example from accepted answer
  substring(name,1,PATINDEX('% %',name)) as First_name,
  SUBSTRING(name,patindex('% %',name),LEN(name)) as Last_Name
FROM (values('Henk de Vries'), ('Thomas')) x(name)

Result

Firstname LastName       First_name Last_Name
Henk      de Vries       Henk       de Vries
Thomas                              Thoma
t-clausen.dk
  • 43,517
  • 12
  • 59
  • 92
1
DECLARE @Name    VARCHAR(50) = 'Henk de Vries'

SELECT SUBSTRING(@Name, 1, CHARINDEX(' ', @Name) - 1) AS [First Name],
       SUBSTRING(@Name, CHARINDEX(' ', @Name) + 1, LEN(@Name)) AS [Last Name]
mohan111
  • 8,633
  • 4
  • 28
  • 55
  • 2
    FYI: While your answer appears plausible, it has appeared in the 'low quality posts' queue after being flagged for deletion. I suspect this is because it is a code only answer without explanation. – Oliver Matthews Jun 13 '14 at 08:23
  • i have just taken the result set and written the code according to that why it has been considered has low quality post i have written the query according to database standards.@OliverMatthews give a reason for it ??? – mohan111 Jun 13 '14 at 08:41
0

Use CHARINDEX to find the first space, then LEFT and RIGHT to extract the user's first and last name

left(username, charindex(' ', username)-1) firstname, 
right(username, (len(username)-charindex(' ', username))) lastname
Raphaël Althaus
  • 59,727
  • 6
  • 96
  • 122
Fabricator
  • 12,722
  • 2
  • 27
  • 40