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