How to remove duplicate words from a string comparing to other string in SQL

Question

I need to check string2 if there are words that I already have in string1 and remove those.

declare @Text1 nvarchar(500) = 'apple, orange, pear'
declare @Text2 nvarchar(500) = 'banana, apple'

The output should be 'banana'

Edit: I just realized I made the question not properly. So, this is right:

DECLARE @Tab TABLE (Ingredients nvarchar(500)) 

insert @Tab select 'apple, orange, pear' 

insert @Tab select 'banana, apple' 

insert @Tab select 'pear, mango'

declare @Ingredients nvarchar(4000) = ''

select @Ingredients = @Ingredients + value + ',' from @Tab cross apply STRING_SPLIT(Ingredients, ',')

SELECT @Ingredients

current result: apple,orange,pear,banana,apple,pear,mango,

expected result: apple,orange,pear,banana,mango,

Your data model is fairly broken as far as SQL goes. You should instead be storing each fruit name in a separate row in a _table_. — Tim Biegeleisen, Jun 01 '19 at 07:40
https://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns — Roger Clerkwell, Jun 01 '19 at 07:47

Flavio Francisco · Answer 1 · 2019-06-03T07:27:54.310

So if that is the case I would like to suggest you to create a function:

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    CREATE FUNCTION GetUniqueValues 
    (
        @LIST AS NVARCHAR(MAX)
    )
    RETURNS NVARCHAR(MAX)
    AS
    BEGIN

        DECLARE @POS INT
        DECLARE @LEN INT
        DECLARE @VALUE NVARCHAR(MAX)

        SET @POS = 0
        SET @LEN = 0

        DECLARE @RESULT NVARCHAR(MAX) = ''

        WHILE CHARINDEX(',', @LIST, @POS + 1) > 0
        BEGIN

            SET @LEN = CHARINDEX(',', @LIST, @POS + 1) - @POS
            SET @VALUE = RTRIM(LTRIM(SUBSTRING(@LIST, @POS, @LEN)))

            IF (CHARINDEX(@VALUE, @RESULT) = 0)
                BEGIN
                    IF (LEN(RTRIM(LTRIM(@RESULT))) = 0)
                        BEGIN
                            SET @RESULT = @VALUE
                        END
                    ELSE
                        BEGIN
                            SET @RESULT = @RESULT + ', ' + @VALUE
                        END         
                END    
            SET @POS = CHARINDEX(',', @LIST, @POS + @LEN) +1        
        END

        RETURN @RESULT

    END
    GO

You can use this function like this based on you example:

SELECT dbo.GetUniqueValues(@Ingredients)

Expected result

apple, orange, pear, banana, mango

This one works, but I just realized I made the question not properly. Please see my question again. — Peter Rudo, Jun 02 '19 at 20:28

score 0 · Answer 2 · answered Jun 01 '19 at 09:06

You can try this:

DECLARE @Text1 NVARCHAR(500)= 'apple, orange, pear';
DECLARE @Text2 NVARCHAR(500)= 'banana, apple';
SELECT *
INTO #temp
FROM
(
    SELECT *
    FROM fn_split(@Text1, ',')
) a;
SELECT *
INTO #temp1
FROM
(
    SELECT *
    FROM fn_split(@Text2, ',')
) b;
SELECT item
FROM #temp1 a
WHERE NOT EXISTS
(
    SELECT item
    FROM #temp b
    WHERE a.item = b.item
);

score 0 · Accepted Answer · answered Jun 01 '19 at 12:24

you can use something like this:

DECLARE @Text1 NVARCHAR(500)= 'apple, orange, pear';
DECLARE @Text2 NVARCHAR(500)= 'banana, apple, cucumber, pear';

--convert to xml for further split using nodes
declare @xmlText1 as xml =  convert(xml, Concat('<a>',replace(@Text1,',','</a><a>'),'</a>'))
declare @xmlText2 as xml =  convert(xml, Concat('<a>',replace(@Text2,',','</a><a>'),'</a>'))

--remove duplicates from @text2 that exist in @text1 
;with cte (val) as (
        select ', ' + value
        from (
            select ltrim(n.value('.', 'VARCHAR(10)')) AS value 
            from  @xmlText2.nodes('a') as t(n)
            except
            select ltrim(n.value('.', 'VARCHAR(10)')) AS value 
            from  @xmlText1.nodes('a') as t(n)
        ) as t 
        for xml path ('')
)
-- reassign @text2
select @Text2 = stuff(val,1,1,'') from cte

--output
select @Text2 as newText2

result

How to remove duplicate words from a string comparing to other string in SQL

3 Answers3