sqlsql-server

split firstname and lastname but only on first space

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
Solution

  • 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