Obter nome e sobrenome consulta SQL Server

Obter nome e sobrenome a partir de um campo Nome Completo (FullName)

SELECT
[FullName],
CHARINDEX(‘ ‘, [FullName]),
CASE WHEN CHARINDEX(‘ ‘, [FullName]) > 0 THEN
LEFT([FullName],CHARINDEX(‘ ‘,[FullName])-1)
ELSE
[FullName]
END as FIRST_NAME,
CASE WHEN CHARINDEX(‘ ‘, [FullName]) > 0 THEN
SUBSTRING([FullName],CHARINDEX(‘ ‘,[FullName])+1, ( LEN([FullName]) – CHARINDEX(‘ ‘,[FullName])+1) )
ELSE
NULL
END as LAST_NAME
FROM TableName

Exemplo de teste:

Declare @temp table ( [FullName] varchar(100) )

insert into @temp ( FullName )
VALUES ( ‘joão mário’ ), (‘josé souza’), (‘alguém’)

SELECT
[FullName],
CHARINDEX(‘ ‘, [FullName]),
CASE WHEN CHARINDEX(‘ ‘, [FullName]) > 0 THEN
LEFT([FullName],CHARINDEX(‘ ‘,[FullName])-1)
ELSE
[FullName]
END as FIRST_NAME,
CASE WHEN CHARINDEX(‘ ‘, [FullName]) > 0 THEN
SUBSTRING([FullName],CHARINDEX(‘ ‘,[FullName])+1, ( LEN([FullName]) – CHARINDEX(‘ ‘,[FullName])+1) )
ELSE
NULL
END as LAST_NAME
FROM @temp