SQL Server : returning multiple rows by ROWID by using only one where / LIKE OPERATOR

Question

Here are rows in my database which I want to get:

I want to get get all 3 rows by executing one query, this ids are received as procedure parameters, sometimes I can receive 1 id and sometimes I can receive 10 of them, depends what users sends to database.

I wrote something like this:

SELECT * 
FROM Products
WHERE CONVERT(NVARCHAR(MAX), ProductId) LIKE '%' + 'A9472294-CFDD-40AC-BC2D-00E39AF4A300, 9A817E40-E4B1-4487-A376-010DD6377E38, 078A3C75-C442-4D88-A1E0-0118B8706667' + '%'

SELECT * 
FROM Products
WHERE 'A9472294-CFDD-40AC-BC2D-00E39AF4A300, 9A817E40-E4B1-4487-A376-010DD6377E38, 078A3C75-C442-4D88-A1E0-0118B8706667' LIKE '%' + CONVERT(NVARCHAR(MAX), ProductId) + '%'

How come the second example returns rows as expected and the first example does not return any rows?

The definition says: %or% finds any values that have "or" in any position

How does this actually work? Could anyone explain?

EDIT:

I thought this LIKE operator should be used on my column like

Select * From Products Where ProductId Like '%' + 'SomeStringId' + '%';

because LIKE operator is used in a WHERE clause to search for a specified pattern in a column and my column is ProductId so I can't understand how come example like this works:

Select * From Products Where 'SomeStringId' Like '%' + ProductId + '%';

How come this example above works if Like is not used on my column, it's used on some string acctually...

in your first LIKE statement you are looking for the entire string with commas and it wont match the full string of all them combined. You cant do a LIKE with multiple values in a comma string, anything between the single quote is compareing to that entire string — Brad, Jan 10 '19 at 20:13
You'd probably be better off using `WHERE CONVERT(NVARCHAR(MAX), ProductID) IN ('A9472294-CFDD-40AC-BC2D-00E39AF4A300', '9A817E40-E4B1-4487-A376-010DD6377E38', '078A3C75-C442-4D88-A1E0-0118B8706667')` — Steve-o169, Jan 10 '19 at 20:16
and how are you getting the multiple values to your SP? As a single string comma delimited or as multiple paramaters? You need to parse it out into a temp table or multiple strings and do a like statement using those — Brad, Jan 10 '19 at 20:16
@Brad gettting as a string comma delimited as in my example, and how come second query works? — Roxy'Pro, Jan 10 '19 at 20:16
adding to Steves above if you get comma seperated list, turn into a table variable or temp table and use an IN like he described on the data that is coverted into a table, that way it wont matter how many are sent it will look for all of them — Brad, Jan 10 '19 at 20:17
The way you are doing this is going to be slow-- you want to convert your input to GUID to match the DB not convert the DB data to a string. Remember a GUID is stored as a number so any of these conversions are going to be very very slow. — Hogan, Jan 10 '19 at 20:17
Your second one works because you are comparing a single value in the table to the long string with all 3 values in it. So you will find the single value in your long list of strings — Brad, Jan 10 '19 at 20:20
Going to the underlying problem there are better ways to do this see https://stackoverflow.com/q/337704 — Conrad Frix, Jan 10 '19 at 20:27
@Brad Well I don't understand LIKE operator really well, this confuses me, I thought The LIKE operator is used in a WHERE clause to search for a specified pattern in a column so that means I should says MyColumn Like '%something%' and in my example I said : SOMELARGESTRING LIKE '%mycolumn%' .. and how come it returns rows from my table as expected? — Roxy'Pro, Jan 10 '19 at 20:46
Basically, the point is that in the first query, you are comparing `ProductID` to `%A9472294-CFDD-40AC-BC2D-00E39AF4A300, 9A817E40-E4B1-4487-A376-010DD6377E38, 078A3C75-C442-4D88-A1E0-0118B8706667%` which means that unless `ProductID` has the exact match for the string between the `%`s, you will not get any results. However, the second query takes the long string and checks to see if `%ProductID%` exists within that string. — Steve-o169, Jan 10 '19 at 20:53
@Steve-o169 check for my Edit, I understand it, but I'm not sure how come this works, so if ProductId exist in long string it will return row with that ProductId obliviously but I am little bit confused it works like that.. I thought LIKE must be used on my column like ProductId like .. something.. check for my edit please... — Roxy'Pro, Jan 10 '19 at 20:58
`LIKE` can be used to compare any string to any other string, whether it be string like column, column like string, column like column, string like string, column like string+column, string like string+column, etc... — ZLK, Jan 10 '19 at 21:06

score 1 · Answer 1 · answered Jan 10 '19 at 22:25

You should split the string and then use an inner join.

For SQL Server below 2016 with the cumbersome string split via XML:

SELECT p.*
       FROM products p
            INNER JOIN (SELECT arg_xml_node.xml_node.value('(.)[1]', 'uniqueidentifier') uniqueidenfier
                               FROM (SELECT convert(xml,
                                            concat('<x>',
                                                   replace('A9472294-CFDD-40AC-BC2D-00E39AF4A300, 9A817E40-E4B1-4487-A376-010DD6377E38, 078A3C75-C442-4D88-A1E0-0118B8706667',
                                                           ', ',
                                                           '</x><x>'),
                                                   '</x>')) xml) arg_xml
                                    CROSS APPLY arg_xml.xml.nodes('x') arg_xml_node (xml_node)) arg_uniqueidenfier
                       ON arg_uniqueidenfier.uniqueidenfier = p.productid;

For SQL Server 2016 and above with the elegant way using string_split():

SELECT p.*
       FROM products p
            INNER JOIN (SELECT convert(uniqueidentifier, ltrim(value)) uniqueidenfier
                               FROM string_split('A9472294-CFDD-40AC-BC2D-00E39AF4A300, 9A817E40-E4B1-4487-A376-010DD6377E38, 078A3C75-C442-4D88-A1E0-0118B8706667',
                                                 ',')) arg_uniqueidenfier
                       ON arg_uniqueidenfier.uniqueidenfier = p.productid;

db<>fiddle

check for my edit please, I was little bit confused about LIKE there, I couldnt understand that I can use like on my string like myString like '%MyDatabaseId%' and I get rows from db, I thought Like works on my databse column like '%MyDatabaseId%' LIKE myString — Roxy'Pro, Jan 11 '19 at 08:22

SQL Server : returning multiple rows by ROWID by using only one where / LIKE OPERATOR

1 Answers1