This SQL is meant to show the changes that will be made, when removing a selected user's email address from a batch.
However, when executed, each row is duplicated, and in the duplication, the semi-colon or comma isn't removed.
For example, if I wanted to remove user "sam@mail.com"

The table results displayed would be:

Row 1:
BatchID: 50
ParamName:EmailTo
ParamValue: jack@mail.com;sam@mail.com;frank@mail.com
NewParamValue: jack@mail.com;frank@mail.com

Row 2:
BatchID: 50
ParamName:EmailTo
ParamValue: jack@mail.com;sam@mail.com;john@mail.com
NewParamValue: jack@mail.com;;frank@mail.com

Ideally, it should only display each row once, and not have the semicolon error.
It seems to be a union error, because when I comment out the First and second union statements, it runs fine.



-- Delete email address from a.Batch

IF(@EmailAddress IS NOT NULL)
BEGIN
IF(LEN(@EmailAddress) > 0)
BEGIN
IF(@ShowOnly = 1)
BEGIN
SELECT DISTINCT BatchID, Name ParamName, Value ParamValue, NewParamValue
FROM (
SELECT *, REPLACE(VALUE, @EmailAddress + ',', '') NewParamValue
FROM a.Batch
WHERE VALUE LIKE '%' + @EmailAddress + ',' + '%'
UNION
SELECT *, REPLACE(VALUE, @EmailAddress + ';', '') NewParamValue
FROM a.Batch
WHERE VALUE LIKE '%' + @EmailAddress + ';' + '%'
UNION
SELECT *, REPLACE(VALUE, @EmailAddress, '') NewParamValue
FROM a.Batch
WHERE VALUE LIKE '%' + @EmailAddress + '%'
) emails
END


Any help is much appreciated

Duplicates are being returned because the rows are not really duplicates. There is some difference between them.
The culprit is likely the "SELECT *", which is not good practice. Enumerate only the columns you need.

VALUE?

This is a forum for SQL Server, not DB2