asked on

Why is the output of this function is like this?

I had this question after viewing Following an example - removing duplicate strings.

The function I have in the link above works. I changed it but now I notice the output has broken "<br/>" tags. Some of the tags look like this
br/>

This is SQl 2014
This is the function

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 have this sample data

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

This is how I call it

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

Open in new window

You see how the output looks like This is a sample

Commission is null.No Standard Cost found for MW for date 2016-09-11<br/>br/>

Vitor Montalvão

Is better you tell us what you pretend to do since I the function isn't doing what you want, right?

Camillia

ASKER

I want to remove duplicate sentences. For example
Commission is null.No Standard Cost found for MW for date 2016-09-21

is repeated twice. That function does it...removes the dups...but replaces some strings (I think) with br>

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Camillia

ASKER

I'll try that