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
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:19 AM
Points: 1,880, Visits: 3,017
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
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:19 AM
Points: 1,880, Visits: 3,017
No problem, glad you found a solution!
Post #1443012
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse