Post reply

SSRS expressions to get part of a string

  • January 30, 2013 at 8:02 pm

    #273473

    Hi,

    I need to write expression in my report to get the characters between "_" and "-".

    I have two columns in my report, group_number and group_name. Using the characters in the group_name, I need to write the group_number.

    For example,

    Suppose the group_name is:

    abc_def-ghi

    Then my group_number should be "def".

    And the number of characters can vary from 2 to 3. That is, group number can be "de" or "def". The thing is it should be between underscore(_) and (-).

    Thanks in advance.

  • February 4, 2013 at 4:14 am

    #1583878

    You can do it in SQL in the dataset, or an expression in the report itself, both using substring and charindex/indexof:

    SQL:

    SUBSTRING(group_name, CHARINDEX('_', group_name), CHARINDEX('-', group_name) - CHARINDEX('_', group_name))

    Expression:

    =Substring(Fields!group_name.Value, Fields!group_name.IndexOf('_'), Fields!group_name.IndexOf('-') - Fields!group_name.IndexOf('_'))

    Note both are untested & you might need to put in something to handle cases where _ or - don't appear.

    Cheers

    Gaz

  • April 14, 2013 at 2:20 pm

    #1606321

    Hi Gaz,

    Thanks for the response.

    I tried the below expression and it worked.

    =Mid(Fields!group_name.Value, InStr(Fields!group_name.Value,"_")+1, (InStr(Fields!group_name.Value, "-") - InStr(Fields!group_name.Value, "_") - 1))

  • April 16, 2013 at 2:29 pm

    #1607048

    No problem, glad you found a solution!

  • June 1, 2015 at 2:43 pm

    #1802165

    Well I'm trying the same thing here and I get a "#error" in the field using the following expression, what am I doing wrong...:

    =Mid(Fields!storageDimensionCode.Value,InStr(Fields!storageDimensionCode.Value," "),(InStr(Fields!storageDimensionCode.Value," ") - InStr(Fields!storageDimensionCode.Value,"H")))

    Thank you all for posting.... 😎

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

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