How do I make wildcards work in a like operator in SQL Server reporting services?

Question

I have added a filter expression using the like operator. What do I put in the value field? my parameter is named @test and I'd like the filter to function as a like %@test% would in SQL. I want it to act like %@test% so I can pass '' to get all values for the query.

Answer 1

I got this to work by adding a filter expression with a like operator and setting the value to this:

="*" + Parameters!Roles.Value + "*"

To get all values I pass ''.

Answer 2

You could also simply use string concatenation in the original Dataset that you want to filter on. You can access the parameters in the same manner as SQL:

WHERE fieldname LIKE '%' + @test + '%'

You can then link the report parameter @test to the @test in the dataset via the parameters option on Dataset Properties.

Answer 3

you could use this simple query.

WHERE fieldname LIKE CONCAT('%',@test,'%')

Used CONCAT

Answer 4

You should use this Query in the DATA SECCTION of the REPORT WIZARD.

SELECT * FROM HR.JOBS WHERE JOB_TITLE LIKE  :JOB_TITLE | |'%'

Answer 5

You need to set Parameter Value with Like operator and that will work like a charm ! (use double quotes(") near following example instead of single quote(') as the editor was not allowing me to put double quotes with asterisk(*) symbol)

LIKE "asterisk" & Parameters!Roles.Value & "asterisk"