substring function Error.....

[DLu]

select name, datalength(Name),
charindex('_2_', Name),
substring(name, 5, charindex('_2_', Name) - 0)
from msdb.sysjobs.name
name
-------------------- ----------- ----------- -----------
Job_4927_2_7Sun 30 9 4927_2_7S
Job_250144_2_6Sat 34 11 250144_2_6S
Job_30197_2_1Mon 32 10 30197_2_1M

but when I use following - 3)
select name, datalength(Name),
charindex('_2_', Name),
substring(name, 5, charindex('_2_', Name) - 3)
from msdb.sysjobs.name

I get the result I want (last column):
Job_4927_2_7Sun 30 9 4927_2
Job_250144_2_6Sat 34 11 250144_2
Job_30197_2_1Mon 32 10 30197_2

but also with an error:
Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.

thanks
David

[MAK]

Substring requires a positive lenght parameter

Substring(string,start,len)

when you pass charindex is length it is a starting position of a found string.
When you do a charindex(...) - 1 it becomes negative.



select substring('MAK',1,0)
-- returns NULL
select substring('MAK',1,-1)
--Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.

[DLu]

But I do want to substring(name, 5, charindex('_2_', Name) - 3)

is there a way around this dilima?

also 'funy' that the above -3 got error from SQL2k STD and with NO error from a MSDE running (both are sp3)?!

thanks
David

[MAK]

yes.

use case statement

select String=case charindex('A','MAK',1) when 0 then substring('MAK',1,0) else
substring('MAK',1,charindex('A','MAK',1)) end