Ok as the title suggests this is occuring.
When using the REPLACE
function on char(185) for data in a temporary table, ones ("1") are being removed as well.
SQL SERVER 2008R2
UNICODE Data
DB collation – collate SQL_Latin1_General_CP1_CS_AS
Tempdb has default
Any other details I could provide which might be helpful, let me know.
Best Answer
CHAR(185)
, being VARCHAR data and using an 8-bit code page, can be a different character depending on the code page being used (which is determined by the collation of the current database when using theCHAR()
function). In many code pages, including the common Windows-1252 (Latin1_General), the character is a superscript 1 (i.e. "1" ). This character, depending on the collation / code page being used, might have a "best fit" mapping to a regular "1".Also, when using a Windows collation, case-sensitivity can affect the outcome of matches between subscript, superscript, and regular versions of the same characters (seriously! see example below). This also covers
NVARCHAR
/ Unicode data (including UTF-8 inVARCHAR
starting in SQL Server 2019).When needing to match on specific characters you need to use a binary collation (i.e. one ending in
_BIN2
unless on SQL Server 2005 or older, in which case only the_BIN
collations are available).As noted above, Unicode data (regardless of type of collation) works the same as
VARCHAR
data used with a Windows collation (because both scenarios use the same Unicode rules):