Trimming text in SSRS

  • 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.

  • 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*

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

  • Jo Pattyn - Monday, September 17, 2018 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*

    Just an FYI, but DATALENGTH will not ignore white space at the end of a string the way LEN will.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply