TSQL Split Function Skip Duplicates

Question

How can I make this skip duplicates?

The column may be one;two;three;two

Would like a split on ; to skip the second two (any duplicates)

CREATE FUNCTION dbo.Split2
(
    @RowData varchar(max),
    @SplitOn varchar(5)
)  
RETURNS @RtnValue table 
(
    Id int identity(1,1),
    Data varchar(8000)
) 
AS  
BEGIN 
    Declare @Cnt int
    Set @Cnt = 1

    While (Charindex(@SplitOn,@RowData)>0)
    Begin
        Insert Into @RtnValue (data)
        Select 
            Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

        Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
        Set @Cnt = @Cnt + 1
    End

    Insert Into @RtnValue (data)
    Select Data = ltrim(rtrim(@RowData))

    Return
END

Answer

CREATE FUNCTION dbo.Split2
(
    @RowData varchar(max),
    @SplitOn varchar(5)
)  
RETURNS @RtnValue table 
(
    Id int identity(1,1),
    Data varchar(8000)
) 
AS  
BEGIN 
    Declare @Data varchar(8000)
    Declare @Cnt int
    Set @Cnt = 1 

    While (Charindex(@SplitOn,@RowData)>0)
    Begin
        set @Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))
        IF NOT EXISTS (SELECT * FROM @RtnValue WHERE data = @Data) 
        BEGIN
           INSERT INTO @RtnValue (data)
           VALUES (@Data)
        END
        Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
        Set @Cnt = @Cnt + 1
    End
    set @Data = ltrim(rtrim(@RowData))
    IF NOT EXISTS (SELECT * FROM @RtnValue WHERE data = @Data) 
    BEGIN
       INSERT INTO @RtnValue (data)
       VALUES (@Data)
    END

    Return
END

What specifically do you mean by duplicates? Can't you just put a DISTINCT clause in there? — Neil
– Neil, Commented Dec 11, 2012 at 22:40

Indi · Accepted Answer · 2012-12-11 22:59:06Z

1

Check before inserting within while loop. Add a IF NOT EXISTS as follows;

DECLARE @temp VARCHAR(100) --declare a temp variable

While (Charindex(@SplitOn,@RowData)>0)
Begin

   SELECT @temp = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

   IF NOT EXISTS (SELECT * FROM @RtnValue WHERE data = @temp) 
   BEGIN
       INSERT INTO @RtnValue (data)
       VALUES (@temp)
   END

   Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
   Set @Cnt = @Cnt + 1
End

EDIT: Add an IF condition for checking existance

edited Dec 11, 2012 at 22:59

answered Dec 11, 2012 at 22:46

Indi

33.9k7 gold badges61 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

paparazzo Over a year ago

Had to also check the final insert. I almost it had it. I was missing the NOT EXISTS. Thanks

Collectives™ on Stack Overflow

TSQL Split Function Skip Duplicates

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related