sql server - Spilt Name field in SQL into First Name and Last Name -

i have column called name stores entire name. want split first name , last name in select statement. problem name might stored like: mike joe or mike & sarah smith. if 2 names (first , last) can use code:

, substring(name, charindex(' ', name)+1, len(name)-(charindex(' ', name)-1)) [lastname] , left(name, charindex(' ', name)) [firstname] 

but if name "mike & sarah smith" want like: first name: mike & sarah last name: smith

how modify code above handle both situations?

if last part last name can use reverse function right , left functions:

select  left('mike & sarah smith',       len('mike & sarah smith')-charindex(' ',reverse('mike & sarah smith'))) firstname, right('mike & sarah smith',charindex(' ',reverse('mike & sarah smith')))  lastname 

output:

firstname        lastname mike & sarah     smith 

just put name of field instead of 'mike & sarah smith' in code when using query.