Firstname and Lastname from Name in sql throwing error as Invalid length parameter

Question

I am using SQL SERVER 2016.

I am trying to extract Firstname and lastname from Name based on space.

From begining upto fist space it is First name.

and from last space upto string end Last name.

i am trying to split firstname and lastname based on space but where there is only firstname than in output lastname appear as NULL.

DECLARE @TABLE TABLE
(
NAME VARCHAR (500)
)

INSERT @TABLE

SELECT 'RAJ A KHOSLA' UNION ALL
SELECT 'Peter D   Souza' UNION ALL
SELECT 'King' UNION ALL
SELECT 'Queen' UNION ALL
SELECT 'William Roches Gayle pant'

Code:

SELECT
left(NAME, charindex(' ', NAME) - 1) AS 'FirstName',
REVERSE(SUBSTRING(REVERSE(NAME), 1, CHARINDEX(' ', REVERSE(NAME)) - 1))  AS 'LastName'
FROM @TABLE

Error: Invalid length parameter passed to the LEFT or SUBSTRING function.

Expected Output

FirstName|LastName
RAJ  |A KHOSLA
Peter |D   Souza
King| NULL
Queen|NULL
William|Roches Gayle pant

Please help me.

Answer 1

Please try the following solution. It is based on XML and XQuery. XML data model is based on ordered sequences. That's why it is so easy to get to the first and last data element.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, [NAME] VARCHAR (500));
INSERT INTO @tbl ([NAME]) VALUES
('RAJ A KHOSLA'),
('Peter D   Souza'),
('King'),
('Queen'),
(''),
(NULL),
('William Roches Gayle pant');
-- DDL and sample data population, end

    DECLARE @separator CHAR(1) = SPACE(1);

    ;WITH rs AS
    (
    SELECT * 
     , TRY_CAST('<root><r>' + 
     REPLACE([name], @separator, '</r><r>') + 
     '</r></root>' AS XML) AS xmldata
    FROM @tbl
    )
    SELECT * 
     , xmldata.value('(/root/r[1])[1]', 'VARCHAR(100)') AS FirstName
     , xmldata.query('if (count(/root/r) = 1) then <r/> else (/root/r[last()])[1]')
          .value('(.)[1]', 'VARCHAR(100)') AS LastName
     , xmldata.query('data(/root/r[position() gt 1])')
          .value('(.)[1] cast as xs:token?', 'VARCHAR(100)') AS LastName2
    FROM rs;

Answer 2

There is a simple fix for this - you just need to make sure you always have a space available. And - since you are taking everything after the first space as the LastName you do not need to reverse anything.

Declare @tbl Table (ID int Identity Primary Key, [NAME] varchar(500));  
  
 Insert Into @tbl ([NAME])  
 Values ( 'RAJ A KHOSLA')  
      , ('Peter D Souza')  
      , ('King')  
      , ('Queen')  
      , ('William Roches Gayle pant');  
  
 Select *  
      , FirstName = substring(v.FullName, 1, charindex(' ', v.FullName, 1))  
      , LastName = substring(v.FullName, charindex(' ', v.FullName, 1) + 1, len(v.FullName))  
   From @tbl t  
  Cross Apply (Values (concat(t.NAME, ' '))) As v(FullName);  

Of course - if you want to get the 'last' space delimited portion of the string then you do need to use reverse...

Answer 3

Names are too tricky and messy to be done with simple string handling. I strongly recommend you get a mailing list tool for this (I happen to like Melissa Data, but there are several others). Things like "John Van Der Poon", "Johnny 2 Arrows" , "Cher" and other things really need specialized software. The mailing list software will also give you other information about the demographics of the name.

Answer 4

Use a CASE condition to check then data first

DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, [NAME] VARCHAR (500));
INSERT INTO @tbl ([NAME]) VALUES
('RAJ A KHOSLA'),
('Peter D Souza'),
('King'),
('Queen'),
(''),
(NULL),
('William Roches Gayle pant');


select case when CHARINDEX(' ', name) < 1 then null else left(NAME, charindex(' ', NAME) - 1) end AS 'FirstName',
       case when CHARINDEX(' ', name) < 1 then null else REVERSE(SUBSTRING(REVERSE(NAME), 1, CHARINDEX(' ', REVERSE(NAME)) - 1)) end  AS 'LastName'
from @tbl