Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Failover Clustering in the Cloud - Understanding Your Options - Free Webinar
 

SQL Server Reporting Services String Manipulation


By:   |   Last Updated: 2015-11-19   |   Comments (3)   |   Related Tips: > Reporting Services Functions

Problem

What string manipulation functions are available in SQL Server Report Services (SSRS) and how can I use them? Can you provide some examples?

Solution

Inevitably if you work with SSRS any period of time, you will encounter the need to manipulate string characters in one fashion or another. These functions make your job easier as they allow you complete certain tasks using the function as opposed to coding the functionality in pure T-SQL. A great example is attempting to insert carriage returns / line feeds in T-SQL. It is doable, but just not that easy. Completing this task using a SQL Server Reporting Services (SSRS) function is easier to use and navigate through the process.

Some of the functions we will review include:

  • ASC / ASCW
  • CHR / CHRW
  • GetChar
  • InStr / InStrRev
  • Mid
  • StrComp
  • StrDup
  • StrReverse

Many of these functions act in a similar way to their Visual Basic counterpart functions. To work through several example, we will use the Adventure Works databases as the basis for our SSRS report generation. The 2014 versions of the regular and data warehouse databases are available on Codeplex: https://msftdbprodsamples.codeplex.com/releases/view/125550. Once you download and install the SQL Server databases, we will subsequently use SQL Server Data Tools for Business Intelligence (SSDT-BI) for Visual Studio 2013 to develop reports. You can download SSDT-BI from: http://www.microsoft.com/en-us/download/details.aspx?id=42313.

SQL Server Reporting Services ASC and ASCW Functions

The first function set we will review is ASC / ASCW; of course you may be wondering why we have ASC and ASCW. The function without the "W" returns the non Unicode character code integer value for the character used the function. Since it is non Unicode, that means the function will only return integers 0 to 255. Adding the W to the end of the function allows the Unicode character set to be used, and thus can return values 0 to 65535. So we could easily find out the character code for a single quote or an apostrophe using the ASC ("'"). We could do the same with a regular letter such as A by doing ASC("A").


single quote

Capital

As seen in the below results, we can easily use these functions to convert a character field to its character code value.

ASC Results

The one caveat about using the ASC and ASCW functions is that we can convert characters only one by one. If we attempt to convert more than one character at a time, only the first character in a string is evaluated, as illustrated below. Notice even though we requested that "ABC" be converted, only A was translated.

ASC Results

SQL Server Reporting Services CHR and CHRW Functions

The CHR and CHRW functions work in a similar yet directly opposite way to the ASC / ASCW functions. Instead of getting the character code from a value, we instead use a character code to display the related character string. The function ending in W again expands the codes that can be used to include the Unicode character set. As with just about everything in SQL and SSRS, there are different ways to achieve a particular task, but I find it very convenient to use character codes 10 and 13 to insert a carriage return and line feed into a text box. Similarly it is easy to insert an apostrophe / single quote using character code 39. In the screen prints below we show using the CHR function with the three mentioned code values to return a two line text box with an apostrophe.

CHR Funciton
Chr results

These functions are very helpful when dealing with titles or address lines within a report. However if you use an incorrect code the following error message is returned in SSRS.

CHR Error
CHR Error Input

SQL Server Reporting Services GetChar Function

In addition to switching between character code and values, we can also pull out a character from a specific position in text string. The GetChar function provides this exact functionality by retrieving the character in the position specified in the function. A good example for using this function relates to a new coding system being implemented in the medical field in the US. The federal government in the US instituted what is called ICD10 which is a 7 character coding methodology for a patients' diagnosis. The value in a specific position of the code, for instance position 7, defines the character of an injury. Thus, we could, for instance, use the GetChar to get the value in position 7 as displayed below.

GetChar

Get Char Result

If we attempt to retrieve a character value from a position that is outside the range of the string, for example retrieve the character in position 20 of a character string that is only 7 characters long, an error results.

Get char Error

SQL Server Reporting Services InStr and InStrRev Functions

Next on our list of string functions is the InStr and InStrRev functions. These functions both compare one string to another and return the starting position of the first instance of a match. The difference between the two values is InStr starts comparing from the left side while InStrRev starts comparing from the right side. Thus, as shown below, both InStr functions require two required arguments. A third optional argument is also available which allows you to specify binary or text comparison; generally the default is fine.

InStr

InStrRev

The results of the above two functions are displayed in the next illustration; you can see how InStr finds the first instance of "USWNT" coming from the left and finds it starting in position 5. The InStrRev starts searching from the right and finds USWNT starting at position 34. I know what some of you may be saying, "why do you have repeating groups or embedded values in your database?". Great point, and your database design should be better. Unfortunately, all databases we report from do not always come as normalized and in a fashion we would like. Thus, at times, we need to use functions such as InStr to pull out the fields we need.

InStr and InStrRev Results

SQL Server Reporting Services Mid Function

Similar to the GetChar function except that it allows the retrieval of multiple characters, the Mid function extracts a specific number of characters from a specified starting position in a character string. This functionality works just the same as the T-SQL substring function and uses the same number of arguments. As shown next, you specify a string, then a starting position, and finally the number of characters from the starting position to retrieve. We will use the same string as for the InStr function example, but we request the Mid function to start at position 5 and then retrieve the next 9 values. Note if the start position is greater than the length of the string (i.e.. the string is 10 characters long and you specify starting position 25), a blank string is returned.

Mid function
Mid Results

SQL Server Reporting Services StrCompare Function

Rounding out the last three string functions we are going to cover is the StrCompare function. This function is used fairly rarely, and basically returns -1, 0, or 1 based on the sort order comparison of two string values. The function takes 3 arguments, the first two are required and the third is optional : String 1, String 2, Comparison Method (text or binary). If string 1 sorts before string 2, then -1 is returned; if string 1 and 2 sort in the same position, then 0 is returned; finally if string 1 sorts after string 2, then 1 is returned. Of course this function could assist in ranking or grouping of values as higher or lower than some mid point. A StrComp of "Cat" and "Dog" is shown below.

StrComp
StrComp2
StrComp Results

SQL Server Reporting Services StrDup and StrRev Functions

Our final two string functions for this tip are StrDup and StrRev. StrDup "duplicates" or repeats a character by noted number of times. You should note, though, if the string contains more than one character, only the first character is repeated. This situation is shown in the below screen print which uses a string of "I Love SSRS", but only the I is repeated 10 times. StrRev takes a string and reverses it completely, also shown below. I guess that functionality could be used for word search puzzles!

StrDup and StrReverse

To be honest, I have not seen a pure business use for either of these functions, but they are fun items that maybe you can use one day.

Conclusion

In this tip we covered many of the string functions that are available and ready to use in SSRS. Some of the functions, such as ASC and CHR work directly with the character code sets which map to integer values for each character. Others are used for string manipulation, such as Mid, InStr and StrReverse. Finally StrComp allows you to make comparisons between string values. All of these functions provide SSRS with features that may or may not readily available in SQL and thus are great additions to your SSRS toolbox.

Next Steps


Last Updated: 2015-11-19


next webcast button


next tip button



About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, May 05, 2017 - 6:29:04 PM - Edgar Back To Top

 

I want to justify the text inside my textbox in a report

 


Friday, November 20, 2015 - 10:47:44 AM - Lori Kent Back To Top

I wanted to set expression for : color but my result field was a string.  the results appear such as: 10.7%, 7.8%, 8.1% and so on.

Do you know how best to strip out non-numerics in a string to pull just the numerics?  This could be useful in stripping out dollar signs, commas, percentages, etc...

=IIf(CInt(Fields!Result)>7.9,"Red","Black")    - error message that Field cannot be converted to 'Integer'
=IIf(Val(Fields!Result)>7.9,"Red","Black")     - did not error, but did not change the color.
=IIf(FormatNumber(Fields!Result)>7.9,"Red","Black")  - no error, but did not change color.

Interestingly enough, using this failed as well: 

=IIf(Replace(Fields!Result,"%","")>7.9,"Red","Black")  - Field cannot be converted to 'String'.

I've read that languages like javascript have ParseInt, but I've also seen things like:
var num = txt.replace(/[^0-9]/g,''); in js.


Thursday, November 19, 2015 - 5:43:55 PM - Jacque Back To Top

I love all of your tips, they are so helpful.  I've been away from SSRS for a while and forgot you could the character codes.  


Learn more about SQL Server tools