I get an empty set when running your query.
My fault on the empty set. In the Object_ID function you need to schema qualify the table and then add the object type like this:
Another example: i found two productnames:
"ComputerAA1" and "ComputerAA1 "
I tried to do uan update to the second one
set productname= 'ComputerAA1' where productname= 'ComputerAA1 '
I would get a constraint error for duplicates.
In which another question arises how come it let me add productname= 'ComputerAA1 ' if 'ComputerAA1' already existed? and there is a constraint for duplicates?
I guess sql understands 'ComputerAA1' and productname= 'ComputerAA1 ' as two different entities.
You finally hit on it at the end of your statement. SQL Server treats special whitespace characters (Tab, LF, Carriage Return) different from spaces. Thus 'CompterAA1' = 'ComputerAA1 ' (single space) but 'ComputerAA1' != 'ComputerAA1 ' (non-space whitespace character). Basically this means that a character column with trailing spaces will cause a unique constraint error, but a character column with trailing special whitespace characters will not. Thus you will need to work on mapping matching products to a single product without trailing whitespace and then delete the "duplicate" ones with whitespace.
So in your issue you have trailing whitespace, not spaces. Odds are your whitespace is one or more of the following (tab, carriage return, line feed) which you can find using the CHAR() function. Tab is CHAR(9), Line Feed CHAR(10), and Carriage Return CHAR(13). The code to find them would be like this:
CHARINDEX(CHAR(9), productname) > 0 OR -- tab
CHARINDEX(CHAR(10), productname) > 0 OR -- line feed
CHARINDEX(CHAR(13), productname) > 0 -- carriage return[/font]
would len(ProductName + 'x')-1 also count if there is a tab at the end?
Yes it does as does DataLength.