General information
Forum:
Microsoft SQL Server
Environment versions
SQL Server:
SQL Server 2008
>>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.
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only