Invalid length parameter passed to the SUBSTRING function.

Question

Invalid length parameter passed to the SUBSTRING function.

Krasavita

SSC-Insane

Points: 23138
More actions
April 29, 2009 at 9:37 am

#133558

When I ran this I get error meaase:
Invalid length parameter passed to the SUBSTRING function. How can I fix it?Thank you
UPDATE TblCust
SET
tblCust.LAST_NAME =
CASE WHEN LAST_NAME Like '%,%'
THEN SUBSTRING(LAST_NAME, 1, CHARINDEX(' ', LAST_NAME + ',') - 1)
ELSE
CASE WHEN LAST_NAME Like ('%III') THEN
Left(LAST_NAME,Len(LAST_NAME)-3)
ELSE
LEFT(LAST_NAME ,Len(LAST_NAME)-2)
END
END
FROM dbo.TblCust
WHERE
LAST_NAME Like '%,%' Or LAST_NAME Like '% SR' Or LAST_NAME Like '% JR'
Or LAST_NAME Like '% III' Or
LAST_NAME Like '% IV' Or
LAST_NAME Like '% V'

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

I can't quite tell, but it looks like your first Case checks for the presence of a comma, then runs a charindex on a blank space. If that's what you're doing, it will give you that error if there's a comma but no space.

As an aside, you don't need the first else, you just need another when.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Krasavita SSC-Insane Points: 23138 More actions · Answer 2

Krasavita

SSC-Insane

Points: 23138

April 29, 2009 at 9:51 am

#986065

Can you write it I am confused.Thank you

Ron McCullough SSC Guru Points: 63877 More actions · Answer 3

What GSquared is trying to tell you
I can't quite tell, but it looks like your first Case checks for the presence of a comma, then runs a charindex on a blank space. If that's what you're doing, it will give you that error if there's a comma but no space.

Try running this:

SELECT Last_Name, CHARINDEX(' ', LAST_NAME + ',')-1 AS 'Value passed to SUBSTRING' FROM dbo.tblcust

It will return a minus 1 (-1) when you have Last_Name entires such as 'Jones,Sr' - notice lack of a space between the comma and 'SR'

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

GSquared SSC Guru Points: 260824 More actions · Answer 4

Krasavita (4/29/2009)
Can you write it I am confused.Thank you

UPDATE

dbo.TblCust

SET

LAST_NAME = CASE WHEN LAST_NAME LIKE '%,%'

THEN SUBSTRING(LAST_NAME, 1,

CHARINDEX(',', LAST_NAME) - 1)

WHEN LAST_NAME LIKE ('%III')

THEN LEFT(LAST_NAME, LEN(LAST_NAME) - 3)

ELSE LEFT(LAST_NAME, LEN(LAST_NAME) - 2)

END

WHERE

LAST_NAME LIKE '%,%'

OR LAST_NAME LIKE '% SR'

OR LAST_NAME LIKE '% JR'

OR LAST_NAME LIKE '% III'

OR LAST_NAME LIKE '% IV'

OR LAST_NAME LIKE '% V' ;

Try that.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Krasavita SSC-Insane Points: 23138 More actions · Answer 5

Krasavita

SSC-Insane

Points: 23138

April 30, 2009 at 7:01 am

#986619

That's work's. Thank you