1

I am working with a query where i need a feature like "LIKE" and "IN" together, the values are returned by SSRS and i have no control over how to format the string supplied by SSRS..

My scenario is something like as following

SELECT *
FROM suppliers
WHERE supplier_name LIKE IN ('IBM', 'Hewlett Packard', 'DELL', 'Microsoft',)

I want all the supplier names which have 'IBM', 'Microsoft' etc in them.

Ex: 'IBM_ER', 'IBM-PCR', 'EBM_TCC', 'Microsoft-Delta', 'Microsoft-TCC'etc.

I will be receiving list of options as parameter from SSRS, where @Manufacture consists of string as shown above, and i can't control that.

SELECT *
FROM suppliers
WHERE supplier_name LIKE IN (@Manufacturer)

Any work around to achieve this thing ?

Nanu
  • 3,010
  • 10
  • 38
  • 52
  • @Nikhil You could also use subqueries, I'm pretty sure. – VoidKing Mar 14 '13 at 21:33
  • It's funny how people overlook things. From where i see, It is not at all a duplicate. You have no control to modify @Parameter above. Still thanks for whatever .. .. – Nanu Mar 14 '13 at 21:37
  • @Nikhil, You are making it very difficult not to downvote your question... ahem, sry, just had to get that out. I am having trouble understanding what you "Can't Control" and why these answers don't work for you. I am pretty decent at SQL and would like to help if I can, although I am not familiar with SSRS. Perhaps if you could explain why the below answers don't help you, I could try to see what I can do? SQL is soooo powerful, I find it hard to believe that there isn't a way to achieve a solution for you. – VoidKing Mar 14 '13 at 21:46
  • SSRS passes a parameter (ParamB) to SQL, and i do the where clause in this format "select ... from ... where ColumnA IN (@ParamB) " and B would basically be comprised of ('Value1', 'Value2', 'Value3'), all this is done internally, that is why i said, i have no control, because from front end, i only tick options to be passed to SQL via parameter. – Nanu Mar 14 '13 at 21:50
  • So i need to play with SQL without changing the Parameter, it is something new to me as well, but i am up for a try and looking for ideas. – Nanu Mar 14 '13 at 21:53
  • @Nikhil Okay, there it is, I was in the middle of typing something similar to what YvesR just posted. See his answer, I think that's your only viable route, unless SSRS has some more targeted solution, given its environment. – VoidKing Mar 14 '13 at 21:54
  • Yes, looking at it now. And it is indeed appealing. Thanks ! – Nanu Mar 14 '13 at 21:55

4 Answers4

3

Well Nikhil, the first thing you should learn is to stay calm. People are here to help and if they don't understand your question it is maybe you missed posting more details.

Jarrod was right with his answer, but if you have @Manufactor as a string list you can do the following: Split the string and afterwards use it as IN statement.

So first, if you don't have one, create a split function. Here is a example: https://codereview.stackexchange.com/questions/15125/sql-server-split-function-optimized

Then second, modify your statement like this.

SELECT *
FROM suppliers
WHERE supplier_name IN (SELECT [value] FROM dbo.fn_Split(@Manufactor,','))
Community
  • 1
  • 1
YvesR
  • 5,922
  • 6
  • 43
  • 70
  • Thanks Yves, first of all i believe in "Give Respect and Take Respect" policy, i understand people are here to help, but one should also not discourage anyone. On a separate note, i liked your solution. i like unusual questions and their unusual answers. THanks !! – Nanu Mar 14 '13 at 21:59
  • removed the like, was a copy/paste typo. – YvesR Mar 14 '13 at 22:03
  • @Michael - Well now at least, i knew i had to use a function, to split the parameter. Rest i can do it own my own. I liked his answer, being the only valid answer at that time, for giving me the idea to split. – Nanu Mar 14 '13 at 22:06
1

try this 

SELECT *
FROM suppliers
WHERE supplier_name like '%IBM%'
or supplier_name like '%Microsoft%'
or supplier_name like '%Hewlett Packard%'
gefrag
  • 352
  • 2
  • 14
  • Is it just me, or doesn't this seem like the first answer that should come to mind? (Before Unions, I mean). Am I overlooking something about his post? – VoidKing Mar 14 '13 at 21:40
  • Also, just nit-picking, but does anyone else develop a sudden eye-twitch when "OR"s and "AND"s aren't capitalized in a sql query (not to mention "LIKE")? – VoidKing Mar 14 '13 at 21:41
  • Well this answer could have been great, only if i could decide how parameter variable is generated. – Nanu Mar 14 '13 at 21:44
  • You want to pass only one parameter to the query? lets say all the values comma separated? – gefrag Mar 14 '13 at 21:45
  • That is correct, as SSRS throws this value on its own, i can't decide or alter how it passes the values in a string to sql. – Nanu Mar 14 '13 at 21:46
  • Give me an example of the value SSRS throws. – gefrag Mar 14 '13 at 21:48
  • SSRS throws this ('IBM', 'Hewlett Packard', 'Microsoft',) and i can't alter it. – Nanu Mar 14 '13 at 22:08
1

First, create a split function so you can turn the CSV string to rows of data.

Next, join to the results of this function, padding the results of the split with wildcards:

SELECT *
FROM suppliers sup
JOIN dbo.Split(',', @Manufacturer) split
    ON sup.supplier_name LIKE '%' + split.s + '%'
Community
  • 1
  • 1
Michael Fredrickson
  • 36,839
  • 5
  • 92
  • 109
0

What you are looking for is a UNION

SELECT *
FROM suppliers
WHERE supplier_name LIKE '%IBM%'
UNION
SELECT *
FROM suppliers
WHERE supplier_name LIKE '%Hewlett Packard%'
UNION
SELECT *
FROM suppliers
WHERE supplier_name LIKE '%Microsoft%'

since you have added details to your question, you might could call a stored procedure and parse the list of names into a dynamic SQL statement on the server, but it will get very ugly fast, not to mention SQL injection vectors once you go down that path.

  • Jarrod, your answer could have been great, only if i had control over Parameter, which i don't. SSRS is generating Param in a string-value format, which i can't modify. – Nanu Mar 14 '13 at 21:40
  • I guess you are out of luck then. –  Mar 14 '13 at 21:42
  • I appreciate people telling me that i don't have a way out, then people telling me that i have the wrong question. Thanks ! – Nanu Mar 14 '13 at 21:43