selecting multiple values in like operator

Question

I have table SW_TBL_KEYWORD

Keyword       
-------
 ATOP
 APMT
 RSND

This is my test query

DECLARE @string NVARCHAR(MAX)
SELECT @string = 'ATOP,APMT'  

SELECT * 
FROM SW_TBL_KEYWORD 
WHERE Keyword LIKE @string

so i need to pass those multiple values which comes from backend to like operator. Test query is not working.

HELP!!!

you can try something like https://stackoverflow.com/questions/1127088/mysql-like-in — Suresh Kamrushi, Jul 20 '18 at 05:22
Are you sure, you are using `mysql` ? It looks like `sqlserver` code. — Shushil Bohara, Jul 20 '18 at 05:25

score 0 · Answer 1 · answered Jul 20 '18 at 05:23

0

the way you tried it works only with Mysql's "IN" function!

That means you have to do it like

select * from SW_TBL_KEYWORD where Keyword like "ATOP" OR Keyword LIKE "APMT"..

also to mention, that you can use % before, after or before and after so for example

select * from SW_TBL_KEYWORD where Keyword like "%TO%"

will also give you the result "ATOP"..

So if you want to search for more then one word, you will have to build your query dependinung on the number of words!

I dont know exactly how you get your search terms, but if you get it seperated with an "," you can explode that and then literate over it and create your query!

answered Jul 20 '18 at 05:23

Christopher Supertramp

263
1
7

the vaues are dynamic, comes from a dropdownlist, this is just a test and its a sql server query, my bad i added mysql as a tag.. i have actually passed multiple values to the parameter from the dropdownlist, now i have to use like operator or any other relevant one to execute the query. help! – Aayush Rajopadhyaya Jul 20 '18 at 05:40
and yes i'd like to mention, i have used Replace function to iterate but for no avail. – Aayush Rajopadhyaya Jul 20 '18 at 06:35

score 0 · Answer 2 · answered Jul 20 '18 at 05:28

0

will you just replace like @string with REGXP REPLACE(@string, ",", "|") to achieve what you want

select * from SW_TBL_KEYWORD where Keyword REGXP REPLACE(@string, ",", "|")

answered Jul 20 '18 at 05:28

kiran gadhe

733
3
11

The question is about **SQL Server** (Microsoft's RDBMS) - which does **NOT** have a `REGXP` operator/function – marc_s Jul 20 '18 at 06:41
@marc_s The original question had tagged MySQL. I will delete this now. – Tim Biegeleisen Jul 20 '18 at 06:43

Shushil Bohara · Answer 3 · 2018-07-20T08:20:43.263

TRY THIS: for the permanent and flexible solution

Create function Split to achieve this requirement and future use also

CREATE FUNCTION dbo.Split(@sep char(1), @s varchar(512))
RETURNS table
AS
RETURN (
    WITH Pieces(pn, start, stop) AS (
      SELECT 1, 1, CHARINDEX(@sep, @s)
      UNION ALL
      SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
      FROM Pieces
      WHERE stop > 0
    )
    SELECT pn,
      SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
    FROM Pieces
  )

Your table and values

DECLARE @SW_TBL_KEYWORD TABLE(Keyword VARCHAR(500))
INSERT INTO @SW_TBL_KEYWORD VALUES
('ATOP'),
('APMT'),
('RSND')

Logic to achieve your requirement

DECLARE @string VARCHAR(MAX)
SELECT @string='ATOP,APMT'

Split the comma separated value using function you have created and store in table variable or in temporary table

DECLARE @tmp_filter TABLE(keyword VARCHAR(500))
INSERT INTO @tmp_filter
SELECT s FROM dbo.Split(',', @string)

Join the filter table with actual table using Like but after seeing your filter and actual values I think you want the exact match instead of partial using like

SELECT t1.*
FROM @SW_TBL_KEYWORD t1 
INNER JOIN @tmp_filter t2 ON t1.Keyword LIKE ('%' + t2.keyword + '%')

For exact match we can change the join with below line

INNER JOIN @tmp_filter t2 ON t1.Keyword = t2.keyword

NOTE: this is long term solution and useful for the other similar tasks. If you were using SQLSERVER2016 or newest then you coluld use STRING_SPLIT function instead of creating user defined function.

selecting multiple values in like operator

3 Answers3