THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Stacia Misner

Working with Reporting Services Filters – Part 2: The LIKE Operator

In the first post of this series, I introduced the use of filters within the report rather than in the query. I included a list of filter operators, and then focused on the use of the IN operator. As I mentioned in the previous post, the use of some of these operators is not obvious, so I'm going to spend some time explaining them as well as describing ways that you can use report filters in Reporting Services in this series of blog posts.

Now let's look at the LIKE operator. If you write T-SQL queries, you've undoubtedly used the LIKE operator to produce a query using the % symbol as a wildcard for multiple characters like this:

select * from DimProduct where EnglishProductName like '%Silver%'

SNAGHTML91bd74b

And you know that you can use the _ symbol as a wildcard for a single character like this:

select * from DimProduct
where EnglishProductName like '_L Mountain Frame - Black, 4_'

 SNAGHTML92af546

So when you encounter the LIKE operator in a Reporting Services filter, you probably expect it to work the same way. But it doesn't. You use the * symbol as a wildcard for multiple characters as shown here:

Expression Data Type Operator Value
[EnglishProductName] Text Like *Silver*

Note that you don’t have to include quotes around the string that you use for comparison.

Books Online has an example of using the % symbol as a wildcard for a single character, but I have not been able to successfully use this wildcard. If anyone has a working example, I’d love to see it!

UPDATE: Thanks to loyal reader Pravin, I can confirm that the ? character is the wildcard for the single character. 

Published Thursday, December 30, 2010 11:06 AM by smisner
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Pravin Patel said:

Hi Stacia,

You can use ? as a Single character wildcard. I am using the same.

Let me know if you still not able to use single character wild card.

Pravin Patel.

Mumbai.

pravin.mssql@hotmail.com

February 21, 2011 7:31 AM
 

smisner said:

Yes, the ? works as a wildcard for a single character. Thanks, Pravin!

February 28, 2011 6:11 PM
 

Darlene Gerber said:

Stacia,

I'm trying to dynamic change the logo on my report with the following code:

=iif(Fields!DoggName.Value LIKE "*ScoopDogg*","ScoopDoggLogoBW",

"BuyersRaisedTextDogglogoBWforReportingServices")

Inside of my Image Properties, but it's not working.

The value of DoggName is DG Test ScoopDogg

Any suggestions?

May 22, 2013 11:09 AM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement