asked on

SQL 2000 Invalid length parameter passed to the substring function.

I have a query that selects the max value of a column, uses len to count and I then subtract from that so I can remove trailing characters. the total length of the field is alwasy 35 characters. Our newer server (sql 2008) work perfectly, our 2k servers work as long as I don't subtracto more than 12.... i cant figure out what is causing this.. any help would be great

This code works only on the sql 2008 servers - returning TUpgradeLog_3_7_3_3 - dropped the last 16 char

select max(LEFT(UpgradeLogTable,LEN(UpgradeLogTable)-16))as maxVobVer from TDatabaseVersion

Open in new window

This code works on both dropping the last 12 - TUpgradeLog_3_7_3_3_201 - i can reduce the number without issue - as soon as I hit 13 it fails on sql 2k

select max(LEFT(UpgradeLogTable,LEN(UpgradeLogTable)-12))as maxVobVer from TDatabaseVersion

Open in new window

NCSA SCADA

ASKER

the error I recieve is .. Invalid length parameter passed to the substring function.

Member_2_861731

The problem must be that you're getting a negative number in this expression:

LEN(UpgradeLogTable)-16

Try doing this in the SQL 2000:

select UpgradeLogTable from TDatabaseVersion
WHERE LEN(UpgradeLogTable)-16<0

you should at least get one value, and that (or those) are the records that are bugging you.

NCSA SCADA

ASKER

it dosent error , but not what i'm after this if i run this -
TUpgradeLog_3_7_3_3_20120215_135509

select max(UpgradeLogTable) from TDatabaseVersion

Open in new window

I just want to drop all of the following leaving this
3_7_3_3
this value will be different on every server (might be 3_9_3_8) i run it against
so i started by just trying to drop the trailing characters and that is where i got stuck

Member_2_861731

Will the value "always" be like this:

TUpgradeLog_X_Y_Z_R......

What I mean is... Will it always be the string "TUpgradeLog_" followed by "#_#_#_#"?

NCSA SCADA

ASKER

yes - same string in the front that i want to drop.... then the version info i need.. then the 16 characters at the end that I want to drop as well

ASKER CERTIFIED SOLUTION

Member_2_861731

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

NCSA SCADA

ASKER

I have never used that before can you explain how it works