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?

Answer 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))

Answer 2

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)
                )