Is there a LEN() bug in SS2008?

Question

Is there a LEN() bug in SS2008?

Jacob Pressures

SSCertifiable

Points: 5804
More actions
January 3, 2013 at 12:08 pm

#268904

I've basically cutting and pasting several SQL clauses that work and then fail for no known reason.
The issues seems to be with the ISNUMERIC substring and ISNUMERIC(EffAcctNum)= 0
CASE 1
This statement works:
It will work for ALL possible statments. -1 doesn't matter because LENGTH always >0
SELECT *
FROM dbo.Account WITH (NOLOCK)
WHERE AccountNumber in (1555,1556)
AND ISNUMERIC(SUBSTRING(EffAcctNum, 1,LEN(EffAcctNum)-1))=1
IF I add ISNUMERIC(EffAcctNum)= 0 to the above WHERE clause, it doesn't work.
SELECT *
FROM dbo.Account WITH (NOLOCK)
WHERE AccountNumber in (1555,1556)
AND ISNUMERIC(SUBSTRING(EffAcctNum, 1,LEN(EffAcctNum)-1))=1
AND ISNUMERIC(EffAcctNum)= 0
ERROR:
Msg 537, Level 16, State 2, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.
Yet Similar case that works only difference is -1 and start position.
SELECT EffAcctNum, LEN(EffAcctNum)-1 as [Digit_Length]
FROM dbo.Account WITH (NOLOCK)
WHERE AccountNumber in (1555,1556)
AND ISNUMERIC(EffAcctNum)= 0
AND ISNUMERIC(SUBSTRING(EffAcctNum, 2,LEN(EffAcctNum)))=1
CASE 2
The following 2 also work. Its the same substring found in the above.
SELECT EffAcctNum, SUBSTRING(EffAcctNum, 1,LEN(EffAcctNum)-1)
FROM dbo.Account WITH (NOLOCK)
WHERE AccountNumber in (1555,1556)
AND ISNUMERIC(EffAcctNum)= 0
SELECT EffAcctNum, ISNUMERIC(SUBSTRING(EffAcctNum, 1,LEN(EffAcctNum)-1))
FROM dbo.Account WITH (NOLOCK)
WHERE AccountNumber in (1555,1556)
AND ISNUMERIC(EffAcctNum)= 0
But as SOON as I add the ISNUMERIC substring, I have problems!!!!
SELECT EffAcctNum, SUBSTRING(EffAcctNum, 1,LEN(EffAcctNum)-1)
FROM dbo.Account WITH (NOLOCK)
WHERE AccountNumber in (1555,1556)
AND ISNUMERIC(SUBSTRING(EffAcctNum, 1,LEN(EffAcctNum)-1))=1
ERROR:
Msg 537, Level 16, State 2, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.
But there is nothing wrong with the ISNUMERIC substring statement.
Thanks! I don't know why I'm getting these syntax errors. It makes no sense.

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Can you post some sample data please?

One potential reason is that the where clause is NOT guaranteed to be executed before the functions in the select are, hence it may be possible that data that you are filtering out is causing the errors (they're not syntax errors)

p.s. unrelated, but http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jacob Pressures SSCertifiable Points: 5804 More actions · Answer 2

Sorry I was having some trouble the resultset was too large and even the resultset specified above was over 1 million records and would not fit in an attachment.

I created insert statements. Had i done a CSV i could have included more data.

Jacob Pressures SSCertifiable Points: 5804 More actions · Answer 3

Jacob Pressures

SSCertifiable

Points: 5804

January 3, 2013 at 1:30 pm

#1573454

i can increase the sample size if need be.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Regarding IsNumeric, you may want to ready this: http://www.sqlservercentral.com/articles/IsNumeric/71512/.

Jacob Pressures SSCertifiable Points: 5804 More actions · Answer 5

Thanks! I'm going to save that article for reference. It is good to know that a lot of signs and symbols will evaluate to NUMERIC = TRUE including d and e.

It doesn't seem to be relevant here though. But it'll stop a future problem.