CODEWITHSUNDEEP
sql-serverstringcompare
user3396213
user3396213

Reputation: 25

ms sql server string compare to another string with multiple value

MS SQL Server Input Column 1 Cat 1 contains multiple values separated by a blank, Cat2 contains the value to be compared. Desired output column should be 1 if Cat2 value matches to any one of the values in Cat1 and 0 if it doesn't. Delimiter is a space. Something like -- answer = 1 if Cat1=In (cat2) after replacing blanks with comma but I don't understand the syntax to accomplish this. Could anyone help me with this. Thanks in advance.

DECLARE @TABLE TABLE(Cat1 VARCHAR(50),Cat2 VARCHAR(50), answer VARCHAR(50))
INSERT INTO @TABLE VALUES
('3 4 6 36 a b','36','1'),
('5 4 6 36 a b','3','0'),
('7 5 6 46 a b','4','0'),
('7 5 6 46 a b','5','1'),
('3 4 6 36 a b','7','0')
select cat1, cat2 , answer
FROM @TABLE

go

Cat1                        Cat2             output
3 4 6 36 a b                 36                 1
3 4 6 36 a b                 3                  1 
3 4 6 36 a b                 4                  1
3 4 6 36 a b                 7                  0

Dev

Upvotes: 0

Views: 1017

Answers (1)

M.Ali
M.Ali

Reputation: 69504

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE TABLE Test_Table(Cat1 VARCHAR(50),Cat2 VARCHAR(50))
INSERT INTO Test_Table VALUES
('3 4 6 36 a b','36'),
('3 4 6 36 a b','3'),
('3 4 6 36 a b','4'),
('3 4 6 36 a b','7'),
('8 4 6 36 a b','b')

Query 1:

SELECT *
      ,CASE WHEN  Cat1  LIKE '% '+ Cat2
            OR    Cat1  LIKE '% '+ Cat2+ ' %'
            OR    Cat1  LIKE       Cat2+ ' %'
              THEN 1
         ELSE 0 
       END AS [OUTPUT]
FROM Test_Table

Results:

|         CAT1 | CAT2 | OUTPUT |
|--------------|------|--------|
| 3 4 6 36 a b |   36 |      1 |
| 3 4 6 36 a b |    3 |      1 |
| 3 4 6 36 a b |    4 |      1 |
| 3 4 6 36 a b |    7 |      0 |
| 8 4 6 36 a b |    b |      1 |

Upvotes: 1

Related Questions