April 18, 2013 at 7:36 am
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,
April 18, 2013 at 8:20 am
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/
April 18, 2013 at 8:24 am
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 recoverability
April 18, 2013 at 9:24 am
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
April 18, 2013 at 9:38 am
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 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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply