Crystal Reports & SQL Reporting Services Training Consultants
Account Log in:
Username:
Password:
Don't be afraid to say "I'm sorry"
 
Institute of IT Trainers - Freelance Trainer of the Year 2006 & 2009
Liverpool Business Connect Member
  Maximum Impact Solutions Limited - Reporting Solutions, Creating Answers
Reporting Solutions - Creating Answers, Crystal Reports, Dashboarding (Xcelsius) & SQL Reporting Services

The Maximum Impact Solutions Blog Feed ME!!!!

07
March

How Do I Extract Characters from within a String Field in SQL Reporting Services 2008?

During a recent SQL Server Reporting Services 2008 report design project, one of my clients needed to extract some text after the last instance of a specific character of a string field.

In SQL when one needs to extract text from a string field, one usually uses one the following functions:
  • CHARINDEX (exact match), or
  • PATINDEX (fuzzy match)

When one of these functions are used in conjunction with the REVERSE function then one can extract all the text after the last instance of a specified character, e.g.:

SELECT
RIGHT( Tablename.Fieldname, CHARINDEX( ' - ', REVERSE( Tablename.Fieldname) + ' - ' ) - 1 ) as NewField

FROM Tablename

Unfortunately, when creating expressions within SQL Server Reporting Services 2008, the CHARINDEX and PATINDEX functions are not available for use, and therefore are not recognised.


Solution:

As a workaround one can use the following expression to achieve the same result:
=Trim(Right(FieldType!FieldName.FieldProperty, InStr(StrReverse(FieldType!FieldName.FieldProperty), " - ")-1))



If you have any questions, leave us a comment below, or need any assistance, please do not hesitate to Contact Us


1

ashok

06
February
how to extract a character within a string based on that I need change the column...
2

Julia

01
April
I am not clear on what you are asking, but if i wanted to look at a column, based on a values of the field, I would be create a formula using the IIF function of SSRS Hope that helps

MAKE A COMMENT

Name *

Email Address *

Comment *