SQL strange problem. Please help!

Question

SQL strange problem. Please help!

maijoe

SSC Enthusiast

Points: 140
More actions
September 11, 2003 at 5:41 pm

#59364

Hi All,
I have this below query which is run for more than 1 year without any problem. But over a sudden, when I run it, sql return this kind of error:'Invalid length parameter passed to the substring function'.
select chrontel_part#,chrontel_part#=
case when chrontel_part# like '%-tr%' then left(chrontel_part#, patindex('%-tr', chrontel_part#) -1)
else chrontel_part#
end
from mytable
At the beginning, I thought it a data entry error, but it was not. I have same kind of data in my table before. Second, I found that my patindex syntax was not correct. It missed the percentage. I filled it in and the query was fine again. Howerver, I run the above query without filling the percentage, it still runs fine from test server.
I wonder why sql did not give me this kind of error from the beginning instead of now?, and why the same query run on 2 different server, one works while the other one doesn't work?
Does anyone have any clues?Thanks and really like to learn from others.
Minh Vu

Viewing 5 posts - 1 through 4 (of 4 total)

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

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 1

First your check, check if the string contains '-tr' but for LEFT part the string must end with '-tr', thus patindex returns a 0 from which 1 is decducted and then the left is taken of string with a -1 length.



Declare  @chrontel_part Varchar(100)

-- Works

Set @chrontel_part='ABC-tr'

Select patindex('%-tr', @chrontel_part)

Select left(@chrontel_part, patindex('%-tr', @chrontel_part) -1) 

-- Works

Set @chrontel_part='-tr'

Select patindex('%-tr', @chrontel_part)

Select left(@chrontel_part, patindex('%-tr', @chrontel_part) -1) 

-- Oops!

Set @chrontel_part='-trABC'

Select patindex('%-tr', @chrontel_part) -- Returns a zero

Select left(@chrontel_part, patindex('%-tr', @chrontel_part) -1) 

-- Conclusion:If the string does not end with a '-tr' the select is in trouble

maijoe SSC Enthusiast Points: 140 More actions · Answer 2

Yes,that's what I've already checked, and I do have some records which do not end with '-tr', but they did appear in the past and my scrip did not return any error until now. That is my concern and don't understand. It could be a bug of sqlserver.

Minh Vu

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 3

Any NULLs in your data

Declare @chrontel_part Varchar(100)

-- NULL

Set @chrontel_part=NULL

Select patindex('%-tr%', @chrontel_part)

Select left(@chrontel_part, patindex('%-tr%', @chrontel_part) -1) 

Select Left(@chrontel_part,NULL)

BOL 2000:

If either pattern or expression is NULL, PATINDEX returns NULL when the database compatibility level is 70. If the database compatibility level is 65 or earlier, PATINDEX returns NULL only when both pattern and expression are NULL.

Steve Jones - SSC Editor SSC Guru Points: 736627 More actions · Answer 4

Good suggestions above. If it's been working and suddenly doen't, either you have a data problem (most likely) or someone changed the code/schema.

Steve Jones

sjones@sqlservercentral.com

http://www.sqlservercentral.com/columnists/sjones

The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/

http://www.dkranch.net