Avatar of GPSPOW
GPSPOW
Flag for United States of America asked on

Substring error using charindex

Here is a string extraction statement that should work but does not:

substring(Name,1,CHARINDEX(',', Name)-1) AS GuarLname

The data looks similar to this:

SMITH,JOHN

I am getting an Invalid Length Parameter error.

What would be the correct syntax?

Thanks

Glen
Microsoft SQL Server

Avatar of undefined
Last Comment
GPSPOW

8/22/2022 - Mon
Lee

This works

declare @name nvarchar(100)
select @name = 'smith,john'
select substring(@name,1,CHARINDEX(',', @name)-1) AS GuarLname

Open in new window


You may have nulls in your selection?

Put a where clause in to filter out nulls and values that don't have a comma in.
Vitor Montalvão

You'll get that error if a record doesn't have the comma.
ASKER CERTIFIED SOLUTION
Vitor Montalvão

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
GPSPOW

ASKER
Thank you.

You were correct.

I did a query and found 22 records out of 500,000 with no commas in the name.

Glen
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck