By: Scott Murray | Updated: 2015-11-19 | Comments (3) | Related: > Reporting Services Development
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").
As seen in the below results, we can easily use these functions to convert a character field to its character code value.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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!
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
- Visual Basic Functions, most of which are available in SSRS - https://msdn.microsoft.com/en-us/library/32s6akha(v=vs.90).aspx
- Check out all of the SQL Server Reporting Services tips.
About the author
Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2015-11-19
