SQL - First and last name from full name

Question

I have a field, in as SQL table, with the fullname of the users that shows the employee number and then the full name (20284 - JOAQUIM MIGUEL SAMPAIO PEREIRA)

I only want to show "JOAQUIM PEREIRA".

Right now am trying to use the following code:

SELECT left(NMSTRING, CHARINDEX(' ',NMSTRING,CHARINDEX(' ',NMSTRING,CHARINDEX(' ',NMSTRING)+1)+1)-1) +
substring(WFPROCATTRIB.NMSTRING, len(WFPROCATTRIB.NMSTRING)-CHARINDEX(' ', REVERSE(WFPROCATTRIB.NMSTRING))+1, len(WFPROCATTRIB.NMSTRING))
FROM WHATEVER

And the result i am getting is: "20284 - JOAQUIM PEREIRA"

How can i remove the "20284 - " part?

Consider using http://stackoverflow.com/questions/159567/sql-parse-the-first-middle-and-last-name-from-a-fullname-field as a guide? — Takarii, Jul 19 '16 at 12:47

score 1 · Answer 1 · answered Jul 19 '16 at 10:54

If the employee number is always five characters, you could simply do:

 select substring(NMString, 9, len(NMString))

Another method would be to use charindex():

select substring(NMString,
                 charindex(' - ', NMString) + 3,
                 len(NMString)
                )

Anuj Tripathi · Answer 2 · 2016-07-19T11:08:35.790

You can use substring to get the desired result

DECLARE @Name varchar(100) = '20284 - JOAQUIM PEREIRA'
SELECT SUBSTRING(@Name, CHARINDEX('-', @Name) + 1, LEN(@Name))

More examples:

DECLARE @TestTable TABLE
(
  EmployeeDetails varchar(100)

 )

 INSERT INTO @TestTable VALUES ('20284 - JOAQUIM PEREIRA'),
 ('123 - Name1') , ('12312344 - Some Other Name')

 SELECT SUBSTRING(EmployeeDetails, CHARINDEX('-', EmployeeDetails) + 1, LEN(EmployeeDetails)) FROM @TestTable

DEMO - ON Stackexchange data explorer

score 1 · Accepted Answer · answered Jul 19 '16 at 11:46

1

Try like this,

DECLARE @sql VARCHAR(50) = '20284 - JOAQUIM MIGUEL SAMPAIO PEREIRA'

SELECT substring(@sql, charindex('-', @sql) + 2, charindex(' ', substring(@sql, charindex('-', @sql) + 2, len(@sql)))) + ' ' + substring(@sql, LEN(@sql) - CHARINDEX(' ', REVERSE(@sql)) + 2, LEN(@sql))

answered Jul 19 '16 at 11:46

StackUser

5,370
2
24
44

Hello. This one did exactly what i needed. – Bruno Nobre Jul 20 '16 at 15:37
I am glad to assist you. – StackUser Jul 21 '16 at 06:55

score 0 · Answer 4 · answered Jul 19 '16 at 12:02

A little bit overkill, but should work for you:

--Declare table with example
DECLARE @WHATEVER TABLE (
    NMSTRING nvarchar(max)
)

INSERT INTO @WHATEVER VALUES
(N'20284 - JOAQUIM MIGUEL SAMPAIO PEREIRA'),
(N'20285 - JOAQUIM SAMPAIO PEREIRA'),
(N'20286 - JOAQUIM PEREIRA')

--Declare xml variable
DECLARE @xml xml

--Put data in xml variable
SELECT @xml = (
    SELECT CAST('<s><n>' + REPLACE(NMSTRING,' ','</n><n>') + '</n></s>' as xml)
    FROM @WHATEVER
    FOR XML PATH('')
)

The data in @xml will look like this:

<s>
  <n>20284</n>
  <n>-</n>
  <n>JOAQUIM</n>
  <n>MIGUEL</n>
  <n>SAMPAIO</n>
  <n>PEREIRA</n>
</s>
<s>
  <n>20285</n>
  <n>-</n>
  <n>JOAQUIM</n>
  <n>SAMPAIO</n>
  <n>PEREIRA</n>
</s>
<s>
  <n>20286</n>
  <n>-</n>
  <n>JOAQUIM</n>
  <n>PEREIRA</n>
</s>

Then run this

SELECT t.v.value('n[3]','nvarchar(max)') + ' ' +
        COALESCE(t.v.value('n[6]','nvarchar(max)'),t.v.value('n[5]','nvarchar(max)'),t.v.value('n[4]','nvarchar(max)'))
FROM @xml.nodes('/s') as t(v)

Output

JOAQUIM PEREIRA
JOAQUIM PEREIRA
JOAQUIM PEREIRA

SQL - First and last name from full name

4 Answers4