split firstname and lastname but only on first space [duplicate]

Question

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

Answer 1

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