Link to home
Start Free TrialLog in
Avatar of GPSPOW
GPSPOWFlag 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
Avatar of Lee
Lee
Flag of United Kingdom of Great Britain and Northern Ireland image

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.
You'll get that error if a record doesn't have the comma.
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of 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