Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

avoiding duplicates in comma separated values column Expand / Collapse
Author
Message
Posted Thursday, April 18, 2013 7:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 21, 2014 12:38 PM
Points: 8, Visits: 281
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,
Post #1443857
Posted Thursday, April 18, 2013 8:20 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:10 AM
Points: 13,230, Visits: 12,709
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1443896
Posted Thursday, April 18, 2013 8:24 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:45 AM
Points: 40,456, Visits: 36,911
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1443901
Posted Thursday, April 18, 2013 9:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 21, 2014 12:38 PM
Points: 8, Visits: 281
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
Post #1443943
Posted Thursday, April 18, 2013 9:38 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:10 AM
Points: 13,230, Visits: 12,709
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 ',' + Item
from 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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1443952
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse