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