• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 110
  • Last Modified:

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
0
GPSPOW
Asked:
GPSPOW
  • 2
1 Solution
 
Lee SavidgeCommented:
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.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You'll get that error if a record doesn't have the comma.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Try this solution:
SELECT substring(Name,1,CASE CHARINDEX(',', Name)
          WHEN 0 THEN LEN(Name)
	  ELSE CHARINDEX(',', Name)-1
    END)  AS GuarLname
FROM YourTableName

Open in new window

0
 
GPSPOWAuthor Commented:
Thank you.

You were correct.

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

Glen
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now