1

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.

  • Please read this and edit your question to add the additional detail. – Michael Green Oct 9 at 1:44
  • Yeah youre right I forgot the collation as well. which turned out to be the problem. thanks though – Doug Coats Oct 9 at 13:32
2

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 the CHAR() 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 in VARCHAR 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).

SELECT CHAR(185)
WHERE  CHAR(185) = '1' COLLATE SQL_Latin1_General_CP1_CI_AS
-- no rows (SQL Server collation, code page 1252, case insensitive)



SELECT CHAR(185)
WHERE  CHAR(185) = '1' COLLATE SQL_Latin1_General_CP437_CI_AS
-- 1 row (SQL Server collation, code page 437, case insensitive)

SELECT CHAR(185)
WHERE  CHAR(185) = '1' COLLATE SQL_Latin1_General_CP437_CS_AS
-- 1 row (SQL Server collation, code page 437, case sensitive)



SELECT CHAR(185)
WHERE  CHAR(185) = '1' COLLATE Latin1_General_100_CI_AS
-- 1 row (Windows collation, code page 1252, case insensitive)

SELECT CHAR(185)
WHERE  CHAR(185) = '1' COLLATE Latin1_General_100_CS_AS
-- no rows (Windows collation, code page 1252, case sensitive)



SELECT CHAR(185)
WHERE  CHAR(185) = '1' COLLATE Latin1_General_100_BIN2;
-- no rows (Windows collation, code page 1252, _BIN2 collation; always works!)

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):

SELECT NCHAR(185)
WHERE  NCHAR(185) = N'1' COLLATE Latin1_General_100_CI_AS
-- 1 row (Windows collation, Unicode data, case insensitive)

SELECT NCHAR(185)
WHERE  NCHAR(185) = N'1' COLLATE Latin1_General_100_CS_AS
-- no rows (Windows collation, Unicode data, case sensitive)
0

In case anyone wants to know - the issue was collation. When I wrote the question I didn't realize the #temp table I was using had a different collation than the default for the database I was working within. It was a simple solution and honestly something I shouldve thought of long before coming here with questions.

  • 2
    Hi Doug. Please add some details to this question. Like the two collations being used (current DB and tempdb), and specifically how you fixed it (e.g. did you specify the collation when creating the temp table? did you specify the COLLATE clause in the query? did you use DATABASE_DEFAULT or did you use a specific collation?, etc). Thanks. Also, please see my answer for a full explanation of what was happening and another option to fix, which might apply to more situations than just this one :-). – Solomon Rutzky Oct 9 at 14:10
  • @solomonRutzky i solved the issue by collating the info into the temp table. – Doug Coats Oct 9 at 16:38
  • Thanks. If you don't mind, can you please edit this answer to add some detail for others having this problem who might find this question and need some guidance? What you've said so far, even "collating the info into the temp table" isn't going to help most people, I don't think. Not asking for a lot, just a sentence or two with specifics, such as: "Added COLLATE DATEBASE_DEFAULT to the column in the CREATE TABLE statement" or "Added COLLATE Latin1_General_100_CS_AS to the column in the SELECT ... INTO #TempTable statement, else it used tempdb's collation: Latin1_General_CI_AS". – Solomon Rutzky Oct 9 at 18:49
  • @SolomonRutzky I def will when I have a minute - i had planned to just havent been able to do so (on a different network here and have to wait till i get home to to access the sql script i have saved). – Doug Coats Oct 9 at 18:56
  • Doug, ok, sounds good...thanks! – Solomon Rutzky Oct 9 at 19:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.