I need to insert this character '●' into a
VARCHAR column ... with collation set as
SQL_Latin1_General_CP1_CI_AS ... The reason I am doing this exercise is that our app was inserting this character into DB earlier.
No, the app was not inserting this character (Black Circle U+25CF) into a
VARCHAR column using a
[SQL_]Latin1_General... Collation. Latin1_General Collations use Code Page 1252 which does not have such a character, nor does there appear to be a "best fit" mapping to convert it to something similar.
That character does exist in some code pages, such as those used by the Korean and Japanese Collations, as the following queries show (both are double-byte character sets, which is why the conversion to
VARBINARY shows two bytes instead of one):
SELECT CONVERT(VARCHAR(10), N'●' COLLATE Korean_100_CI_AS),
CONVERT(VARBINARY(10), CONVERT(VARCHAR(10), N'●' COLLATE Korean_100_CI_AS));
-- ● 0xA1DC
SELECT CONVERT(VARCHAR(10), N'●' COLLATE Japanese_XJIS_100_CI_AS),
CONVERT(VARBINARY(10), CONVERT(VARCHAR(10), N'●' COLLATE Japanese_XJIS_100_CI_AS));
-- ● 0x819C
SELECT CONVERT(VARCHAR(10), N'●' COLLATE Latin1_General_100_CI_AS),
CONVERT(VARBINARY(10), CONVERT(VARCHAR(10), N'●' COLLATE Latin1_General_100_CI_AS));
-- ? 0x3F
It is possible, however, that the app was inserting '•' (Bullet U+2022), which looks quite similar, just a little smaller. The "Bullet" character is available in Code Page 1252 (Dec 149 or Hex 0x95).
our DB Team wrote a script to correct it and apparently it got replaced with "?"
Well, replacing "●" with "?" doesn't sound like fixing it ;-).
Now I am just trying to reproduce this scenario to ask them why it is getting replaced with "?" because we are replacing it as empty in the App. Since my App already has a fix for this issue (we remove anything greater than 127)
Regardless of technicalities, it seems fairly obvious both statements cannot be true at the same time: it cannot get replaced with "?" and get removed before hitting SQL Server. Clearly the app code does not have a fix for this, and is not removing everything with a value over 127. It is being converted into "?" in SQL Server because that character is being inserted yet does not exist in Code Page 1252.
I think it is done because it says "COLLATE" in the update query.
No, the forcing of a binary Collation via the
COLLATE keyword is not changing this character into "?". Both the column's Collation (
SQL_Latin1_General_CP1_CI_AS ) and the explicit Collation (
Latin1_General_BIN ) use Code Page 1252, so no characters could change due to this.
The "●" character is being replaced with "?" as it is being inserted into the
VARCHAR column. And once it is inserted as a question mark, there is nothing to clean up, and no way to identify that question mark as being originally from something else as opposed to an intended question mark.
Is this a fine script to remove Non-ASCII-Data > 127? Can someone explain this query in plain English?
That query finds any occurrences of characters having an ASCII value of greater than 127 (that is what the
PATINDEX does), and then gets any such character (that is what the
SUBSTRING does), and then replaces all occurrences of that character in the column with empty string (this is what the
REPLACE does). The query does not do any
UPDATE if no characters are found having an ASCII value over 127.
This query only works on one character at a time. So, if a column has 2 or more characters with ASCII values above 127 that are not the same ASCII value, then the script would need to be executed multiple times.
That query will not help in this particular case (i.e. of replacing "●" with "?") since that conversion is happening on the way in. This query only handles characters with ASCII values of 128 - 255, yet "●" is not one of those characters as it could never be in this column in the first place.