-2

How to compare a string delimited string to a column value in sql without considering sequence?

Suppose I have a value in sql column [fruits] - mango, apple, cherry... I have list in asp.net C# cherry, mango, apple... I want to write sql query such that it can match sql table without order.

Dovydas Šopa
  • 2,282
  • 8
  • 26
  • 34
faux
  • 91
  • 1
  • 10

3 Answers3

0

I suggest that you look at the fabulous answers in this SO question

How to split a comma-separated value to columns

That said, your solution should be pass each column which contains words to this function and then store it in a table along with a column ID.

So "mango,apple,cherry" becomes a table with values

ColdID   Value
_______________
1      mango
1      apple
1      cherry

Now order the tables by ColID ASC, Value ASC and compare both the tables.

Community
  • 1
  • 1
DhruvJoshi
  • 17,041
  • 6
  • 41
  • 60
  • Please don't use the accepted answer at the link posted. That answer is using a loop to split strings which is horribly inefficient. By far the best way is to not store delimited data but if you do check out the split options here. http://sqlperformance.com/2012/07/t-sql-queries/split-strings Many options which are set based and don't use loops. – Sean Lange May 17 '17 at 13:22
0

This should do it.

DECLARE @str NVARCHAR(MAX)
,   @Delim NVARCHAR(255)

SELECT @str = 'cherry,mango,peach,apple'
SELECT @Delim = ','

CREATE TABLE #Fruits ( Fruit VARCHAR(255) )

INSERT INTO #Fruits
        ( Fruit )
    VALUES  ( 'cherry' ),
            ( 'Mango' ),
            ( 'Apple' ) ,
            ( 'Banana' )


   ;WITH lv0 AS (SELECT 0 g UNION ALL SELECT 0)
    ,lv1 AS (SELECT 0 g FROM lv0 a CROSS JOIN lv0 b) -- 4
    ,lv2 AS (SELECT 0 g FROM lv1 a CROSS JOIN lv1 b) -- 16
    ,lv3 AS (SELECT 0 g FROM lv2 a CROSS JOIN lv2 b) -- 256
    ,lv4 AS (SELECT 0 g FROM lv3 a CROSS JOIN lv3 b) -- 65,536
    ,lv5 AS (SELECT 0 g FROM lv4 a CROSS JOIN lv4 b) -- 4,294,967,296
    ,Tally_CTE (n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM lv5)

SELECT SUBSTRING(@str, N, CHARINDEX(@Delim, @str + @Delim, N) - N) AS Item
    INTO #StrTable
    FROM Tally_CTE
    WHERE N BETWEEN 1 AND DATALENGTH(@str) + DATALENGTH(@Delim)
        AND SUBSTRING(@Delim + @str, N, LEN(@Delim)) = @Delim;

--#############################################################################
-- in both
--############################################################################# 
SELECT *
    FROM #Fruits F
        JOIN #StrTable ST ON F.Fruit = ST.Item

--#############################################################################
-- in table but not string
--############################################################################# 
SELECT *
    FROM #Fruits F
        LEFT JOIN #StrTable ST ON ST.Item = F.Fruit
    WHERE ST.Item IS NULL
--#############################################################################
-- in string but not table
--############################################################################# 
SELECT *
    FROM #StrTable ST
        LEFT JOIN #Fruits F ON ST.Item = F.Fruit
    WHERE F.Fruit IS NULL
GO
DROP TABLE #Fruits
DROP TABLE #StrTable
Jay Wheeler
  • 379
  • 2
  • 7
0

You can use string_split function to do this. I tested this on SQL Server 2017 ctp 2.0 but it should work on 2016 too.

 drop table if exists dbo.Fruits;

create table dbo.Fruits (
    Fruits varchar(100)
);


insert into dbo.Fruits (Fruits)
values ('cherry,mango,apple'), ('peanut,cherry,mango'), 
('apple,cherry,mango')


declare @str varchar(100) = 'apple,mango,cherry';

select
    tt.Fruits
    , COUNT(tt.value) as Value01
    , COUNT(app.value) as Value02
from (
    select
    *
    from dbo.Fruits f
        outer apply string_split (f.Fruits, ',') t
) tt
    left join string_split (@str, ',') app on tt.value = app.value
group by tt.Fruits
Dean Savović
  • 739
  • 3
  • 7