Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSRS expressions to get part of a string Expand / Collapse
Author
Message
Posted Wednesday, January 30, 2013 8:02 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 22, 2013 5:34 PM
Points: 3, Visits: 30
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.
Post #1413824
Posted Monday, February 4, 2013 4:14 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 28, 2015 12:47 PM
Points: 2,385, Visits: 4,450
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
Post #1415215
Posted Sunday, April 14, 2013 2:20 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 22, 2013 5:34 PM
Points: 3, Visits: 30
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))
Post #1442134
Posted Tuesday, April 16, 2013 2:29 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 28, 2015 12:47 PM
Points: 2,385, Visits: 4,450
No problem, glad you found a solution!
Post #1443012
Posted Monday, June 1, 2015 2:43 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 21, 2015 12:28 PM
Points: 6, Visits: 50
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....
Post #1690638
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse