1

I have the following table:

Object  Field  Values
------------------------------------
1       1      A;A;A;B;A;A
2       1      A;B;C;C
2       2      X
3       1      X;Y;Z
3       2      V;V;V;V;V;V;V;V;V;V;V

How can I select from this table only the unique values from the concatenated values? So:

Object  Field  Values
---------------------
1       1      A;B
2       1      A;B;C
2       2      X
3       1      X;Y;Z
3       2      V

In any scripting language, I would loop through the values from Values, explode on ; and loop through that array with some logic filtering out duplicates. However, I need to do this only using SQL (Server 2008).

Can anybody tell me if and how this can be done?

Any help is greatly appreciated :-)

Improve this question
7
  • 3
    You should normalize your table and not have data like that. Commented Jul 3, 2014 at 12:36
  • 1
    Thanks, but it is what I have to work with. This is the input I get and I have no influence over the DB design, nor can I create tables. Commented Jul 3, 2014 at 12:38
  • I suggest a scalar function. Commented Jul 3, 2014 at 12:41
  • Yo need to write an SQL function and pass the values and that function has to have code to remove duplicates. Commented Jul 3, 2014 at 12:41
  • 1
    How are the values created? That is probably the right place to remove the duplicates. Commented Jul 3, 2014 at 13:22

3 Answers 3

Reset to default
3

To do this first create a split function. This is the one I use but if you search the internet (or even SO) for "SQL Server Split Function" you will find a number of alternatives if you don't like this:

ALTER FUNCTION [dbo].[Split](@StringToSplit NVARCHAR(MAX), @Delimiter NCHAR(1))
RETURNS TABLE
AS
RETURN
(   
    SELECT  ID = ROW_NUMBER() OVER(ORDER BY n.Number),
            Position = Number,
            Value = SUBSTRING(@StringToSplit, Number, CHARINDEX(@Delimiter, @StringToSplit + @Delimiter, Number) - Number)
    FROM    (   SELECT  TOP (LEN(@StringToSplit) + 1) Number = ROW_NUMBER() OVER(ORDER BY a.object_id)
                FROM    sys.all_objects a
                        CROSS JOIN sys.all_objects b
            ) n
    WHERE   SUBSTRING(@Delimiter + @StringToSplit + @Delimiter, n.Number, 1) = @Delimiter
);

Then you can split your field, So running:

SELECT  t.Object, t.Field, s.Value
FROM    T
        CROSS APPLY dbo.Split(t.[Values], ';') AS s

Will turn this:

Object  Field  Values
------------------------------------
1       1      A;A;A;B;A;A

into:

Object  Field  Values
------------------------------------
1       1      A
1       1      A
1       1      A
1       1      B
1       1      A
1       1      A

Then you can apply the DISTINCT Operator:

SELECT  DISTINCT t.Object, t.Field, s.Value
FROM    T
        CROSS APPLY dbo.Split(t.[Values], ';') AS s;

To give:

Object  Field  Values
------------------------------------
1       1      A
1       1      B

Then you can concatenate your rows back into a single column giving a final query:

SELECT  t.Object, t.Field, [Values] = STUFF(x.value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM    T
        CROSS APPLY 
        (   SELECT  DISTINCT ';' + s.Value
            FROM    dbo.Split(t.[Values], ';') AS s
            FOR XML PATH(''), TYPE
        ) AS s (x)

SQL Fiddle appears to be down, but once you have the Split function created the below is a full working example:

CREATE TABLE #T (Object INT, Field INT, [Values] VARCHAR(MAX));
INSERT #T
VALUES
    (1, 1, 'A;A;A;B;A;A'),
    (2, 1, 'A;B;C;C'),
    (2, 2, 'X'),
    (3, 1, 'X;Y;Z'),
    (3, 2, 'V;V;V;V;V;V;V;V;V;V;V');

SELECT  t.Object, t.Field, [Values] = STUFF(x.value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM    #T AS T
        CROSS APPLY 
        (   SELECT  DISTINCT ';' + s.Value
            FROM    dbo.Split(t.[Values], ';') AS s
            FOR XML PATH(''), TYPE
        ) AS s (x);

EDIT

Based on your comment that you can't create tables or modify the DDL, I thought I would account for the situation where you can't create a function either. You can expand the above split function out into your query, so you don't actually need to create a function:

CREATE TABLE #T (Object INT, Field INT, [Values] VARCHAR(MAX));
INSERT #T
VALUES
    (1, 1, 'A;A;A;B;A;A'),
    (2, 1, 'A;B;C;C'),
    (2, 2, 'X'),
    (3, 1, 'X;Y;Z'),
    (3, 2, 'V;V;V;V;V;V;V;V;V;V;V');

SELECT  t.Object,
        t.Field,
        [Values] = STUFF(x.value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM    #T AS T
        CROSS APPLY 
        (   SELECT  DISTINCT ';' + SUBSTRING(t.[Values], Number, CHARINDEX(';', t.[Values] + ';', Number) - Number)
            FROM    (   SELECT  TOP (LEN(t.[Values]) + 1) Number = ROW_NUMBER() OVER(ORDER BY a.object_id)
                        FROM    sys.all_objects a
                                CROSS JOIN sys.all_objects b
                    ) n
            WHERE   SUBSTRING(';' + t.[Values] + ';', n.Number, 1) = ';'
            FOR XML PATH(''), TYPE
        ) AS s (x);
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

This is the most awesome answer ever. Thanks a bunch :D
2

Here is a standalone solution:

DECLARE @t table(Object int, Field int, [Values] varchar(max))
INSERT @t values
(1, 1, 'A;A;A;B;A;A'),
(2, 1, 'A;B;C;C'),
(3, 1, 'X'),
(4, 1, 'X;Y;Z'),
(5, 1, 'V;V;V;V;V;V;V;V;V;V;V')

SELECT t.Object, t.Field, x.[NewValues]
FROM @t t
CROSS APPLY
(
  SELECT STUFF((
     SELECT distinct ';'+t.c.value('.', 'VARCHAR(2000)') value
     FROM (
         SELECT x = CAST('<t>' + 
               REPLACE([Values], ';', '</t><t>') + '</t>' AS XML)
     ) a
     CROSS APPLY x.nodes('/t') t(c)
        for xml path(''), type 
    ).value('.', 'varchar(max)'), 1, 1, '') [NewValues] 
) x

Result:

Object Field  NewValues
1      1      A;B
2      1      A;B;C
3      1      X
4      1      X;Y;Z
5      1      V

According to @GarethD's comment this may perform slow.

Test data:

create table #t(Object int identity(1,1), Field int, [Values] varchar(max))
INSERT #t values
(1, 'A;A;A;B;A;A'),(1, 'A;B;C;C'), (1, 'X'), (1, 'X;Y;Z'),(1, 'V;V;V;V;V;V;V;V;V;V;V')

insert #t select field, [values] from #t union all select field, [values] from #t union all select field, [values] from #t
insert #t select field, [values] from #t union all select field, [values] from #t union all select field, [values] from #t
insert #t select field, [values] from #t union all select field, [values] from #t union all select field, [values] from #t
insert #t select field, [values] from #t union all select field, [values] from #t union all select field, [values] from #t
insert #t select field, [values] from #t union all select field, [values] from #t union all select field, [values] from #t
insert #t select field, [values] from #t union all select field, [values] from #t union all select field, [values] from #t

Performance testing my script:

SELECT t.Object, t.Field, x.[NewValues]
FROM #t t
CROSS APPLY
(
  SELECT STUFF((
     SELECT distinct ';'+t.c.value('.', 'VARCHAR(2000)') value
     FROM (
         SELECT x = CAST('<t>' + 
               REPLACE([Values], ';', '</t><t>') + '</t>' AS XML)
     ) a
     CROSS APPLY x.nodes('/t') t(c)
        for xml path(''), type 
    ).value('.', 'varchar(max)'), 1, 1, '') [NewValues] 
) x

Result less than 1 sec.

Performance testing Garath script

(had to edit testdata to get all rows. Identical rows were considered as 1 row):

WITH CTE AS
(   SELECT  DISTINCT t.Object, t.Field, s.Value
    FROM    #T AS T
            CROSS APPLY 
            (   SELECT  ID = ROW_NUMBER() OVER(ORDER BY n.Number),
                        Position = Number,
                        Value = SUBSTRING(t.[Values], Number, CHARINDEX(';', t.[Values] + ';', Number) - Number)
                FROM    (   SELECT  TOP (LEN(t.[Values]) + 1) Number = ROW_NUMBER() OVER(ORDER BY a.object_id)
                            FROM    sys.all_objects a
                                    CROSS JOIN sys.all_objects b
                        ) n
                WHERE   SUBSTRING(';' + t.[Values] + ';', n.Number, 1) = ';'
            ) AS s
)
SELECT  Object,
        Field,
        [Values] = STUFF((SELECT ';' + Value
                        FROM CTE AS T2
                        WHERE T2.Object = T.Object
                        AND T2.Field = T.Field
                        FOR XML PATH(''), TYPE
                        ).value('.', 'VARCHAR(MAX)'), 1, 1, '')

FROM    CTE AS T
GROUP BY Object, Field;

Result 6 seconds

If any row has null in values this script will also crash.

Improve this answer

4 Comments

@Anon it had to have a value. I suppose varchar(max) could have been better.
A nice neat solution, but there can be performance issues with using xml to split a delimited list, also this does not handle special xml characters. i.e trying to split '>;>;<;<' will throw an error XML parsing: ... illegal qualified name character unless you adapt to use the solution at the bottom of this article
@GarethD thanks for your comment. This may fail on special characters. I compared our scripts and the performance issues were there. However it was not in my script.
You were right about the performance issues with mine, but it was not due to the split function. It was to do with the fact that I had built the query up in a step by step manor to explain it, rather than choosing the most efficient method. I've updated my answer with more effecient methods using the same split function. Then repeated your test with on additional row added to the initial data: (1, REPLICATE('A;B;C;D;', 1000)). Resulting in - XML Split: ~90 seconds. Numbers split: ~6 seconds. This is the performance difference I was talking about due to the way XML is streamed in blocks.
0

Just as a Scalar Value Function alternative without the CTE...

ALTER FUNCTION [SplitRemoveDupes] (
@String VARCHAR(MAX)
,@Delimiter VARCHAR(5)
)
RETURNS VARCHAR(MAX)
AS
BEGIN

DECLARE @SplitLength INT 
DECLARE @DedupedValues VARCHAR(MAX) 

DECLARE @SplittedValues TABLE 
( 
  OccurenceId SMALLINT IDENTITY(1,1), 
  SplitValue VARCHAR(200) 
)  

    WHILE LEN(@String) > 0
    BEGIN
        SELECT @SplitLength = (
                CASE CHARINDEX(@Delimiter, @String)
                    WHEN 0
                        THEN LEN(@String)
                    ELSE CHARINDEX(@Delimiter, @String) - 1
                    END
                )

    INSERT INTO @SplittedValues
    SELECT SUBSTRING(@String, 1, @SplitLength)

    SELECT @String = (
            CASE (LEN(@String) - @SplitLength)
                WHEN 0
                    THEN ''
                ELSE RIGHT(@String, LEN(@String) - @SplitLength - 1) END)

END

SET @DedupedValues=(SELECT DISTINCT STUFF((
            SELECT DISTINCT (@Delimiter + SplitValue)
            FROM @SplittedValues s
            ORDER BY (@Delimiter +  SplitValue)
                        FOR XML PATH('')
            ), 1, 1, '') AS a
FROM @SplittedValues ss)

RETURN @DedupedValues 
END

Call it inline...

SELECT Object, Field, [dbo].[SplitRemoveDupes](Values,';') From Table
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.