September 17, 2018 at 2:43 am
Hello
I have a data field being output into a report that gives me, for example:
'Reference Number: 878655555 detail, detail, lots of text etc'
Basically the only text I want to show in my report is the number in bold.
Please can someone advise how to use LEFT and RIGHT (or some other function) to remove superfluous text either side of the data I want to output? I've tried a few combinations but nothing's worked so far.
Thank you.
September 17, 2018 at 4:40 am
added * for testing. Len +2 because len trims the rightmost whitespace.
declare @totrim varchar(50)='Reference Number: 878655555 detail, detail, lots of text etc'
declare @toskip varchar(50)='Reference Number: ';
declare @endofnumber varchar(50)=' detail,';
select CHARINDEX(@toskip,@totrim)
select CHARINDEX(@endofnumber,@totrim)
select '*'+SUBSTRING(@totrim,LEN(@toskip)+2,CHARINDEX(@endofnumber,@totrim)-len(@toskip)-2)+'*'
gives
*878655555*
September 17, 2018 at 5:26 am
Thanks for taking the trouble to reply.
I was also able to use successfully:
=Mid(Fields!my.Value,InStr(Fields!my.Value,":")+1,Instr(InStr(Fields!my.Value,":")+3,Fields!my.Value," ") - InStr(Fields!myValue,":") -1)
September 17, 2018 at 1:30 pm
Jo Pattyn - Monday, September 17, 2018 4:40 AMadded * for testing. Len +2 because len trims the rightmost whitespace.
declare @totrim varchar(50)='Reference Number: 878655555 detail, detail, lots of text etc'
declare @toskip varchar(50)='Reference Number: ';
declare @endofnumber varchar(50)=' detail,';
select CHARINDEX(@toskip,@totrim)
select CHARINDEX(@endofnumber,@totrim)
select '*'+SUBSTRING(@totrim,LEN(@toskip)+2,CHARINDEX(@endofnumber,@totrim)-len(@toskip)-2)+'*'
gives
*878655555*
Just an FYI, but DATALENGTH will not ignore white space at the end of a string the way LEN will.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply