avoiding duplicates in comma separated values column
-
Hi Guys,
I am working on stored proc to update master table from temp table and getting following issue.
temp_table
sa, Logger, Reader
master_table
sa, Reader
Reqired_Output_On_master_table
sa, Reader, Logger
So i only want to add comma seperated value when it is not already present in master table. (to avoid duplicate entry). i think i have to use CHARINDEX but don't know exact implementation. Any help from you guys will be gr8...
Thanks,
-
kalpit_yellow (4/18/2013)
Hi,I am working on stored proc to update master table from temp table and getting following issue.
temp_table
sa, Logger, Reader
master_table
sa, Reader
Reqired_Output_On_master_table
sa, Reader, Logger
So i only want to add comma seperated value when it is not already present in master table. (to avoid duplicate entry). i think i have to use CHARINDEX but don't know exact implementation. Any help from you guys will be gr8...
Thanks,
Hi and welcome to the forums. I would be happy to help but you haven't posted enough details for me to be able to offer much help. If you can post ddl, sample data and desired output I am certain we can figure this out pretty quickly. Please take a few minutes and read the article in my signature about best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
To be bluntly honest, comma-delimited lists are a hint that the table design isn't right. Why a comma-delimited list in a column and not a child table with one row per value? Figuring out duplicates and values not in another set is much easier when you have the values in rows rather than in a single column.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Following script by Ashish Jain fixed my issue
CREATE FUNCTION dbo.DistinctList
(
@List VARCHAR(MAX),
@Delim CHAR
)
RETURNS
VARCHAR(MAX)
AS
BEGIN
DECLARE @ParsedList TABLE
(
Item VARCHAR(MAX)
)
DECLARE @list1 VARCHAR(MAX), @Pos INT, @rList VARCHAR(MAX)
SET @list = LTRIM(RTRIM(@list)) + @Delim
SET @pos = CHARINDEX(@delim, @list, 1)
WHILE @pos > 0
BEGIN
SET @list1 = LTRIM(RTRIM(LEFT(@list, @pos - 1)))
IF @list1 <> ''
INSERT INTO @ParsedList VALUES (CAST(@list1 AS VARCHAR(MAX)))
SET @list = SUBSTRING(@list, @pos+1, LEN(@list))
SET @pos = CHARINDEX(@delim, @list, 1)
END
SELECT @rlist = COALESCE(@rlist+',','') + item
FROM (SELECT DISTINCT Item FROM @ParsedList) t
RETURN @rlist
END
GO
SELECT dbo.DistinctList('342,34,456,34,3454,456,aa,bb,cc,aa',',') DistinctList
GO
-
kalpit_yellow (4/18/2013)
Following script by Ashish Jain fixed my issue CREATE FUNCTION dbo.DistinctList
(
@List VARCHAR(MAX),
@Delim CHAR
)
RETURNS
VARCHAR(MAX)
AS
BEGIN
DECLARE @ParsedList TABLE
(
Item VARCHAR(MAX)
)
DECLARE @list1 VARCHAR(MAX), @Pos INT, @rList VARCHAR(MAX)
SET @list = LTRIM(RTRIM(@list)) + @Delim
SET @pos = CHARINDEX(@delim, @list, 1)
WHILE @pos > 0
BEGIN
SET @list1 = LTRIM(RTRIM(LEFT(@list, @pos - 1)))
IF @list1 <> ''
INSERT INTO @ParsedList VALUES (CAST(@list1 AS VARCHAR(MAX)))
SET @list = SUBSTRING(@list, @pos+1, LEN(@list))
SET @pos = CHARINDEX(@delim, @list, 1)
END
SELECT @rlist = COALESCE(@rlist+',','') + item
FROM (SELECT DISTINCT Item FROM @ParsedList) t
RETURN @rlist
END
GO
SELECT dbo.DistinctList('342,34,456,34,3454,456,aa,bb,cc,aa',',') DistinctList
GO
This will work but if you want to make this faster you can use the DelimitedSplit8K function. It will parse this without using a loop.
declare @List varchar(50) = '342,34,456,34,3454,456,aa,bb,cc,aa'select STUFF((select distinct ',' + Itemfrom dbo.DelimitedSplit8K(@List, ',')FOR XML PATH('')), 1, 1, '')You can find the code for this function by following the link in my signature about splitting strings.
I have to agree with Gail that storing delimited lists in a table is a terrible idea. This does not comply with even first normal form.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply