>Hi
>
>any ideas what I'm doing wrong here.
>
>I'm trying to extract a number from a string (example below). I want to get the numbers that come after batchNo
>
>I'm getting the start position of the string and the end position but if I take one from the other in the query to give me the length it gives a different result
>
>any ideas.
>
>Thanks
>
>my result is
>
>string_start string_end string_length (No column name)
>19 24 21 685 | PayinInSlipNo: 27
>
>declare @comments varchar(200)
>set @comments = 'Feb2011 | BatchNo: 685 | PayinInSlipNo: 2719 | bank Ref: P11D2B'
>
>
>select charindex('Batchno',@comments)+8 as string_start,
>charindex('|',@comments,charindex('Batchno',@comments)) as string_end,
> (charindex('|',@comments,charindex('Batchno',@comments)) - charindex('Batchno',@comments)+8) as string_length,
> SUBSTRING(@comments,charindex('Batchno',@comments)+8 ,charindex('|',@comments,charindex('Batchno',@comments)))

Shouldn't the second parameter to SUBSTR() be the requested length of the returned string. Seems to me you are passing in the start poistion of the second pipe (i.e. effectively SUBSTR(@comments,19,24) instead of SUBSTR(@comments,19,5))