>>>>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.
>
>Ah. Try:

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

Thats it.

Thaks Viv