Eureka!

Hasn’t it always bugged you that you could never do a LIKE clause with multiple criteria? I found this very limiting, especially so when dealing with multi-valued parameters in MS Reporting Services. I believe to have found a way around this! (I haven’t seen this method used anywhere yet, but I’m sure many of you may be familiar with it.)

In order to make this work, we need to use some association. You will have to create a (temporary) table in which to store your criteria, complete with wildcards. Each criterion should be a separate record. From there we JOIN our target table, which we are comparing the criterion with. Furthermore, we will place a limiter on the JOIN to only allow the selected criteria. This last step is only necessary if your criteria are based on a lookup table (presumable the same one you are using to JOIN) and of which only a subset is selected (that is my situation in one of my reports in MS Reporting Services).

Note that the items in [table A] must be stored with their wildcards, i.e. ‘%criterion1%’, ‘%criterion2%’, etc.

Let’s pseudo-code this out:

1. CREATE TABLE [table A] with criteria to be used in LIKE clause.
2. JOIN criterion table to main table [table B].
3. Limit JOIN to LIKE clause using field [field B] from [table B] and field [field A] from [table A].

Now for some code:

SELECT *
FROM [table B] AS b
JOIN [table A] AS a ON a.[field A] LIKE b.[field B]

If you are using [table A] as a lookup table to establish criteria on a report, of which only a subset may be selected, do the following:

SELECT *
FROM [table B] AS b
JOIN [table A] AS a ON a.[field A] LIKE b.[field B] AND a.[field A] IN (@report_parameter)

where @report_parameter is the resultset from a query, or a comma-separated list of items that exist in [table A]. This is analogous to:

SELECT *
FROM [table B] AS b
JOIN [table A] AS a ON a.[field A] LIKE b.[field B] AND a.[field A] IN (’%criterion1%’, ‘%criterion2%’, ‘%criterion3%’)