1

A table has CSV data and I want to do a SELECT DISTINCT ID on that table, how should I proceed?

+-----------+
| id        |
+-----------+
| 111,1487  |
+-----------+
| 462       |
+-----------+
| 2492      |
+-----------+
| 3184      |
+-----------+
| 3181,3184 |
+-----------+
| 3181      |
+-----------+
| 440       |
+-----------+
| 1436      |
+-----------+

I've got a function which I usually use that can split varchar csv string of INTs into a one-column table which I use as follow:

SELECT number from INTLIST_TO_TBL('123,234,345,4566')

It works well but I can't use it in this case as there are many rows and it will return multiple rows in certain cases.

Should I try using a cursor that inserts into a temporary table then proceed to the next step by querying this temporary table ?

I've been told cursors are synonym of poor performance which is why I always resort to asking here first before going down that road.

2
  • 1
    I hope it is obvious at this point that CSV data in a table column is also a synonym of poor performance
    – datagod
    Sep 12 '17 at 14:24
  • Yes, I wish I was there the day this was modelised to understand the explanation of the person in my company who thought it was a great idea at the time. Intersection tables aren't expensive...
    – A_V
    Sep 12 '17 at 17:30
2

You should be able to use CROSS APPLY.

In an effort to provide a Minimum Complete Verifiable Example, I've included a 'simple' SPLIT TVF which is intended to emulate your INTLIST_TO_TBL function. This is for demonstration purposes only and there are more efficient ways to split a string that are beyond my answer.

IF EXISTS ( SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N'[dbo].[Split]')
                    AND type IN (N'FN', N'IF', N'TF', N'FS', N'FT') ) 
    DROP FUNCTION [dbo].[Split] ;
GO
CREATE FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))        
returns @temptable TABLE (element int,items varchar(8000))        
as        
begin        
    declare @element int=1
    declare @idx int        
    declare @slice varchar(8000)        

    select @idx = 1        
        if len(@String)<1 or @String is null  return        

    while @idx!= 0        
    begin        
        set @idx = charindex(@Delimiter,@String)        
        if @idx!=0        
            set @slice = left(@String,@idx - 1)        
        else        
            set @slice = @String        

        if(len(@slice)>0)
            begin   
                insert into @temptable(Element,Items) values(@element,@slice)        
                set @element=@element+1
            end 

        set @String = right(@String,len(@String) - @idx)        
        if len(@String) = 0 break        
    end    
return        
end


GO

Now that we have the Split TVF, let's see an example of how to incorporate it.

DECLARE @tbl TABLE (id VARCHAR(100))

INSERT @tbl
VALUES ('111,1487')
    ,('462')
    ,('2492')
    ,('3184')
    ,('3181,3184')
    ,('3181')
    ,('440')
    ,('1436')

SELECT DISTINCT b.items
FROM @tbl a
CROSS APPLY [dbo].[Split](a.id, ',') b
1
  • GENIUS. this worked perfectly. So all I had to do is wrap my entire query in as a subquery and select distinct the split result of the cross apply
    – A_V
    Sep 12 '17 at 13:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.