error when splitting full name into first name and last name

Question

When running the below code, I get an error of Invalid length parameter passed to the LEFT or SUBSTRING function.

The names are in the format:

LastName FirstName MiddleName

Only the last name and first name are relevant to me

SELECT SUBSTRING(longname, 1, CHARINDEX(' ', longname + ' ') - 1) AS Firstname,     
       SUBSTRING(longname,
                 CHARINDEX(' ', longname + ' ') + 1,
                 LEN(longname) - CHARINDEX(' ', longname + ' ')) AS Lastname
FROM Table 
WHERE longname IS NOT NULL

I love this article, because it's completely true: [Falsehoods Programmers Believe About Names](https://www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-about-names/). TL;DR: If the name isn't split already, you have no full-proof way of finding out what the first name and last name *really* are, without asking the person themselves or someone who personally knows them. — Thom A, Nov 21 '19 at 12:20
"longname" doesn't contain space , so its throwing "Invalid length parameter passed to the SUBSTRING function." Please the "longname" value or keep condition so if space contain apply the substring function else take longname as firstname — asimshahiddIT, Nov 21 '19 at 12:26
@asimshahiddIT there will always be a space as I'm adding a space. Please see the example above. — Fler, Nov 21 '19 at 12:27
Maybe this would help https://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns . Also, if you are doing this a lot of times, creating a split function does help in the longer run. — ankyskywalker, Nov 21 '19 at 12:31

Gordon Linoff · Accepted Answer · 2019-11-21T13:30:11.493

1

I think the logic for LastName is off. Try this:

SELECT v.Firstname,     
       LEFT(rest, CHARINDEX(' ', rest + ' ') - 1) as LastName
FROM Table t CROSS APPLY
     (VALUES (LEFT(longname, CHARINDEX(' ', longname + ' ') - 1),
              STUFF(longname, 1, CHARINDEX(' ', longname + ' '), '')
             )
     ) v(FirstName, rest)
WHERE longname IS NOT NULL

edited Nov 21 '19 at 13:30

answered Nov 21 '19 at 12:21

Gordon Linoff

1,242,037
58
646
786

Hi Gordon, thanks for the response. I'm receiving an error of Incorrect syntax near 'STUFF'. – Fler Nov 21 '19 at 12:24
1

I *think* Gordon meant there to be comma after `) - 1)`, @Fler , as they define the `VALUES` constructor as returning 2 columns, and the `LEFT` function is closed at that point. – Thom A Nov 21 '19 at 12:27

score 0 · Answer 2 · answered Nov 21 '19 at 12:27

0

"longname" doesn't contain space , so its throwing "Invalid length parameter passed to the SUBSTRING function." Please the "longname" value or keep condition so if space contain apply the substring function else take longname as firstname

answered Nov 21 '19 at 12:27

asimshahiddIT

330
2
5

there will always be a space as I'm adding a space. Please see the example above. – Fler Nov 21 '19 at 12:28

error when splitting full name into first name and last name

2 Answers2