sql server is "value" equal to "value "

Question

I have two Tag values in the same column.

value1 = "(Massachusetts)"
value2 = "(Massachusetts)" + CHAR(10)

I'd like to convert the new line version to "value " (with a blank space) but I get a primary key duplication error.

"Violation of PRIMARY KEY constraint 'Contact.Tag'. Cannot insert duplicate key in object 'dbo.Contact.Tag'. The duplicate key value is (id_num_removed, id_num_removed, (Massachusetts) ). The statement has been terminated"

This is the statement I'm running.

update [Contact.tag]
SET TAG_NAME = '(Massachusetts) '
where
    instance_id = 97986
    and contact_id = 93941676
    and TAG_NAME = '(Massachusetts)' + CHAR(10);

I have tried a version with replace and get the same error.

UPDATE [blah].[dbo].[Contact.Tag]
SET TAG_NAME = REPLACE(TAG_NAME, CHAR(10), ' ')
where TAG_NAME LIKE '%' + CHAR(10) + '%'

It seems like SQL server thinks that "value" = "value "

Any suggestions?

Answer 1

Sql server will not consider Trailing spaces. If you want to duplicate it you better prefix the space character instead of suffix.

update [Contact.tag] SET TAG_NAME = '(Massachusetts) ' where instance_id = 97986 and contact_id = 93941676 and TAG_NAME =Char(10)+ '(Massachusetts)'