Camillia
asked on
Following an example - removing duplicate strings
I found this example
Example
To remove duplicate strings. Mine is not comma delimited. I separate the strings by <br> I changed the function above but it didn't work. How can I do this?
This is SQL 2014
Here's my change
I call it using this string
Example
To remove duplicate strings. Mine is not comma delimited. I separate the strings by <br> I changed the function above but it didn't work. How can I do this?
This is SQL 2014
Here's my change
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+'<br>','') + item
FROM (SELECT DISTINCT Item FROM @ParsedList) t
RETURN @rlist
END
GO
I call it using this string
declare @test varchar(max)
set @test = 'Commission is null.No Standard Cost found for MW for date 2016-09-11<br/> Commission is null.No Standard Cost found for MW for date 2016-09-12<br/>
Commission is null.No Standard Cost found for MW for date 2016-09-13<br/> Commission is null.No Standard Cost found for MW for date 2016-09-14<br/>
Commission is null.No Standard Cost found for MW for date 2016-09-15<br/> Commission is null.No Standard Cost found for MW for date 2016-09-16<br/>
Commission is null.No Standard Cost found for MW for date 2016-09-17<br/> Commission is null.No Standard Cost found for MW for date 2016-09-18<br/>
Commission is null.No Standard Cost found for MW for date 2016-09-19<br/> Commission is null.No Standard Cost found for MW for date 2016-09-20<br/>
Commission is null.No Standard Cost found for MW for date 2016-09-21<br/> Commission is null.No Standard Cost found for MW for date 2016-09-22<br/>
Commission is null.No Standard Cost found for MW for date 2016-09-11<br/> Commission is null.No Standard Cost found for MW for date 2016-09-12<br/>
Commission is null.No Standard Cost found for MW for date 2016-09-13<br/> Commission is null.No Standard Cost found for MW for date 2016-09-14<br/>
Commission is null.No Standard Cost found for MW for date 2016-09-15<br/> Commission is null.No Standard Cost found for MW for date 2016-09-16<br/>
Commission is null.No Standard Cost found for MW for date 2016-09-17<br/> Commission is null.No Standard Cost found for MW for date 2016-09-18<br/>
Commission is null.No Standard Cost found for MW for date 2016-09-19<br/> Commission is null.No Standard Cost found for MW for date 2016-09-20<br/>
Commission is null.No Standard Cost found for MW for date 2016-09-21<br/> Commission is null.No Standard Cost found for MW for date 2016-09-22<br/>
Commission is null.No Standard Cost found for MW for date 2016-09-11<br/> Commission is null.No Standard Cost found for MW for date 2016-09-12<br/>
Commission is null.No Standard Cost found for MW for date 2016-09-13<br/> Commission is null.No Standard Cost found for MW for date 2016-09-14<br/>
Commission is null.No Standard Cost found for MW for date 2016-09-15<br/> Commission is null.No Standard Cost found for MW for date 2016-09-16<br/>
Commission is null.No Standard Cost found for MW for date 2016-09-17<br/> Commission is null.No Standard Cost found for MW for date 2016-09-18<br/>
Commission is null.No Standard Cost found for MW for date 2016-09-19<br/> Commission is null.No Standard Cost found for MW for date 2016-09-20<br/>
Commission is null.No Standard Cost found for MW for date 2016-09-21<br/> Commission is null.No Standard Cost found for MW for date 2016-09-22<br/>
Commission is null.No Standard Cost found for MW for date 2016-09-11<br/> Commission is null.No Standard Cost found for MW for date 2016-09-12<br/>
Commission is null.No Standard Cost found for MW for date 2016-09-13<br/> Commission is null.No Standard Cost found for MW for date 2016-09-14<br/>
Commission is null.No Standard Cost found for MW for date 2016-09-15<br/> Commission is null.No Standard Cost found for MW for date 2016-09-16<br/>
Commission is null.No Standard Cost found for MW for date 2016-09-17<br/> Commission is null.No Standard Cost found for MW for date 2016-09-18<br/>
Commission is null.No Standard Cost found for MW for date 2016-09-19<br/> Commission is null.No Standard Cost found for MW for date 2016-09-20<br/>
Commission is null.No Standard Cost found for MW for date 2016-09-21<br/> Commission is null.No Standard Cost found for MW for date 2016-09-22<br/>
Commission is null.No Standard Cost found for MW for date 2016-09-11<br/> Commission is null.No Standard Cost found for MW for date 2016-09-12<br/>
Commission is null.No Standard Cost found for MW for date 2016-09-13<br/> Commission is null.No Standard Cost found for MW for date 2016-09-14<br/>
Commission is null.No Standard Cost found for MW for date 2016-09-15<br/> Commission is null.No Standard Cost found for MW for date 2016-09-16<br/>
Commission is null.No Standard Cost found for MW for date 2016-09-17<br/> Commission is null.No Standard Cost found for MW for date 2016-09-18<br/>
Commission is null.No Standard Cost found for MW for date 2016-09-19<br/> Commission is null.No Standard Cost found for MW for date 2016-09-20<br/>
Commission is null.No Standard Cost found for MW for date 2016-09-21<br/> Commission is null.No Standard Cost found for MW for date 2016-09-22<br/>
Commission is null.No Standard Cost found for MW for date 2016-09-11<br/> Commission is null.No Standard Cost found for MW for date 2016-09-12<br/>
Commission is null.No Standard Cost found for MW for date 2016-09-13<br/> Commission is null.No Standard Cost found for MW for date 2016-09-14<br/>
Commission is null.No Standard Cost found for MW for date 2016-09-15<br/> Commission is null.No Standard Cost found for MW for date 2016-09-16<br/>
Commission is null.No Standard Cost found for MW for date 2016-09-17<br/> Commission is null.No Standard Cost found for MW for date 2016-09-18<br/>
Commission is null.No Standard Cost found for MW for date 2016-09-19<br/> Commission is null.No Standard Cost found for MW for date 2016-09-20<br/>'
SELECT dbo.DistinctList(@test,'<br>') DistinctList
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What is the expected output for your sample input ?
Try..You may need to do some modification..
----------
ALTER FUNCTION SplitString
(
@Input NVARCHAR(MAX),
@Character NVARCHAR(20)
)
RETURNS @Output TABLE
(
Item NVARCHAR(MAX)
)
AS
BEGIN
DECLARE @StartIndex INT, @EndIndex INT
SET @StartIndex = 1
IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
BEGIN
SET @Input = @Input + @Character
END
WHILE CHARINDEX(@Character, LTRIM(RTRIM(@Input))) > 0
BEGIN
SET @EndIndex = CHARINDEX(@Character, LTRIM(RTRIM(@Input)))
INSERT INTO @Output(Item)
SELECT LTRIM(RTRIM(SUBSTRING(LTRIM(RTRIM(@Input)), @StartIndex, @EndIndex - 1)))
SET @Input = LTRIM(RTRIM(SUBSTRING(LTRIM(RTRIM(@Input)), @EndIndex + LEN(@Character) , LEN(LTRIM(RTRIM(@Input))))))
END
RETURN
END
GO
----------
DECLARE @test NVARCHAR(MAX)
SET @test = 'Commission is null.No Standard Cost found for MW for date 2016-09-11<br/> Commission is null.No Standard Cost found for MW for date 2016-09-12<br/>
Commission is null.No Standard Cost found for MW for date 2016-09-13<br/> Commission is null.No Standard Cost found for MW for date 2016-09-14<br/>
Commission is null.No Standard Cost found for MW for date 2016-09-15<br/> Commission is null.No Standard Cost found for MW for date 2016-09-15<br/>
Commission is null.No Standard Cost found for MW for date 2016-09-15<br/> Commission is null.No Standard Cost found for MW for date 2016-09-15<br/>
Commission is null.No Standard Cost found for MW for date 2016-09-17<br/> Commission is null.No Standard Cost found for MW for date 2016-09-17<br/>
Commission is null.No Standard Cost found for MW for date 2016-09-19<br/> Commission is null.No Standard Cost found for MW for date 2016-09-27<br/>'
SELECT DISTINCT Item FROM dbo.SplitString(@test,'<br/>') DistinctList
ASKER
let me see, thanks