Link to home
Start Free TrialLog in
Avatar of Camillia
CamilliaFlag for United States of America

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
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

Open in new window


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/>' 

Open in new window


SELECT dbo.DistinctList(@test,'<br>') DistinctList

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of chaau
chaau
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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

Open in new window


----------

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

Open in new window

Avatar of Camillia

ASKER

let me see, thanks