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.

Possible duplicate of [T-SQL and the WHERE LIKE %Parameter% clause](http://stackoverflow.com/questions/14237755/t-sql-and-the-where-like-parameter-clause) — Sayed Abolfazl Fatemi, Jan 20 '16 at 10:08
@SayedAbolfazlFatemi that question is re: SQL, this is RE SQL Server Reporting Services which is different. — David Silva Smith, Jan 20 '16 at 17:44

score 18 · Accepted Answer · answered Feb 04 '11 at 13:30

18

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

answered Feb 04 '11 at 13:30

David Silva Smith

11,498
11
67
91

score 18 · Answer 2 · answered Feb 04 '11 at 13:31

18

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.

answered Feb 04 '11 at 13:31

JBarnes

491
1
6
15

For those querying against Oracle, use: WHERE fieldname LIKE '%' || :test || '%' – ashtonium Nov 19 '15 at 13:43

score 0 · Answer 3 · answered Dec 04 '15 at 19:06

0

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

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

answered Dec 04 '15 at 19:06

Daniel LL

1

score 0 · Answer 4 · answered Jul 31 '17 at 07:28

0

you could use this simple query.

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

Used CONCAT

answered Jul 31 '17 at 07:28

Ramgy Borja

2,330
2
19
40

score 0 · Answer 5 · answered Jan 10 '22 at 22:39

0

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"

answered Jan 10 '22 at 22:39

Bhavik Desai

1

1

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](/help/how-to-answer). – Community Jan 11 '22 at 03:37

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

5 Answers5

Linked