Extract first and last name

Question

I have a column named Name in a table called test that has a full name, and I'm trying to extract the first and last name. So I wrote a query like this:

SELECT [Name], LEFT([Name],CHARINDEX(' ',[Name])-1) AS FIRST_NAME, SUBSTRING([Name],CHARINDEX(' ',[Name])+1,LEN([Name])) AS LAST_NAME FROM Test 

But this gives me an error:

Msg 537, Level 16, State 2, Line 1 Invalid length parameter passed to the LEFT or SUBSTRING function.

Thta, because I have some meanings in the name:

Name:

Hopkins

How can I handle this?

Answer 1

 Declare @t table ( [Name] varchar(100) ) insert into @t ( Name ) VALUES ( 'dennis hopper' ), ('keanu reaves'), ('thatgirl') SELECT [Name], CHARINDEX(' ', [Name]), CASE WHEN CHARINDEX(' ', [Name]) > 0 THEN LEFT([Name],CHARINDEX(' ',[Name])-1) ELSE [Name] END as FIRST_NAME, CASE WHEN CHARINDEX(' ', [Name]) > 0 THEN SUBSTRING([Name],CHARINDEX(' ',[Name])+1, ( LEN([Name]) - CHARINDEX(' ',[Name])+1) ) ELSE NULL END as LAST_NAME FROM @t 

Answer 2

The problem with your source code is here:

 CHARINDEX(' ',[Name])-1 

If [Name] does not contain a space, CharIndex returns 0. You subtract 1 and pass this to the left function. When the second parameter on the left is -1, you will get this error. In my opinion, the easiest way to “fix” this problem is to let the CharIndex function find something:

 CHARINDEX(' ',[Name] + ' ')-1 

Now ... this code cannot fail.

You only NEED to make this one place in the source code, but you should also add it to the LAST_NAME part. If you do not, you will get incorrect results (although you will not get an error).

 SELECT [Name], LEFT([Name],CHARINDEX(' ',[Name] + ' ')-1) AS FIRST_NAME, SUBSTRING([Name],CHARINDEX(' ',[Name] + ' ')+1,LEN([Name])) AS LAST_NAME FROM Test 

This query will return the same results as the query suggested by @Sage, but (in my opinion) it is easier to read and easier to understand.

Answer 3

we can also use the localization function

SELECT Name, LEFT (Name, locate ('', Name) -1) AS FIRST_NAME, SUBSTRING (Name, location ('', Name) + 1, LENgth (Name)) AS LAST_NAME FROM test