1

I need to insert this character '●' into a VARCHAR column of a MSSQL database with collation set as SQL_Latin1_General_CP1_CI_AS (or at least mock what my Python + Windows MSSQL Driver might have done). I tried to insert this from my client, but it gets inserted as ?.

The hex value as checked in Python is \xe2\x97\x8f which is binary 226, 151, 143.

The collation spec says that 226 is defined but 143 and 151 is undefined. So my best would be to insert 226?

The reason I am doing this exercise is that our app was inserting this character into DB earlier. At this point, I don't know how it went in because but it went through a Python App with Windows MSSQL Driver and our DB Team wrote a script to correct it and apparently it got replaced with "?".

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), I am trying to mock this directly from back-end to prove that it is getting replaced with '?' which is actually my claim and I think it is done because it says "COLLATE" in the update query.

UPDATE pr_response
SET nur_respon =
REPLACE (nur_respon,
     SUBSTRING(nur_respon, PATINDEX('%[^ !-~]%' COLLATE Latin1_General_BIN, nur_respon), 1),
         '')
WHERE PATINDEX('%[^ !-~]%' COLLATE Latin1_General_BIN, nur_respon) > 0

Is this a fine script to remove Non-ASCII-Data > 127? Can someone explain this query in plain English?

  • Start with a simpler repro: CREATE TABLE dbo.Test (VarcharField VARCHAR(10), NvarcharField NVARCHAR(10)); INSERT INTO dbo.Test (VarcharField, NvarcharField) VALUES ('●','●'); SELECT * FROM dbo.Test; – Brent Ozar Nov 20 '18 at 12:58
  • Thanks @BrentOzar. I am expecting that first column would be inserted but the second won't right? Our DB Team doesn't give us the permission to create a Table, but I will try to execute this somehow. What is the point you are trying to highlight? – Nishant Nov 20 '18 at 13:03
  • 2
    You can try using a temp table instead of a real table, that should work. Brent is trying to show you that you can't store unicode data in a VARCHAR, SQL replaces it with a "?". Your app may have been doing magic with that value, but it will always be stored as a ? in a VARCHAR field. Use NVARCHAR if you need to store unicode data. – Jonathan Fite Nov 20 '18 at 13:35
  • Thank You, all. It could be some wrong observation on my part where I thought it is going as differently. I checked this in my Linux box and it always goes as "?". [In my comment above, I mixed up first and second, sorry for that!] – Nishant Nov 20 '18 at 13:48
1

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.

  • Thank You @SolomonRutzky for a very detailed explanation. By "fix" we just mean removing any NON-ASCII bytes for now. I really am suspecting a mix up between small bullet and big bullet like you suggested :-). That is a good catch. I also had this doubt if this would need to be looped over if there are multiple characters, I think I will bring that to the attention of my DB Team. – Nishant Nov 21 '18 at 4:29
  • 1
    @Nishant You are quite welcome. :-) I understand what you meant by "fix". I was just pointing out that your intended fix in the app code is not working; it is not removing the "●" characters. This is why you see the "?" in the DB. If the app code was removing the "●" characters, then you wouldn't see any evidence of it in the DB. – Solomon Rutzky Nov 21 '18 at 4:54

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.