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.

Queer

Possible duplicate of T-SQL and the WHERE LIKE %Parameter% clause – Raffin

@SayedAbolfazlFatemi that question is re: SQL, this is RE SQL Server Reporting Services which is different. – Queer

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 ''.

Queer

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.

Interknit

For those querying against Oracle, use: WHERE fieldname LIKE '%' || :test || '%' – Recreate

Answer 3

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

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

Algarroba

Answer 4

you could use this simple query.

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

Used CONCAT

Ethyl

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"

Mackenziemackerel

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Perutz