>>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))

Yes sorry Viv that was a bit misleading. My query was why would string_length be returned as 21 when I assumed it would be 5 (24 - 19)

the substring returning '685 | PayinInSlipNo: 27' was a leftover from earlier on.