5

I am using some scripts from another topic, but the accepted answer isn't working for all my data scenarios. I would have asked my question on the original How to check for Non-Ascii Characters post, but I don't have enough reputation to comment or up-vote as of yet.

Questions:

My Testing

I created SQL Fiddle with sample data, the stored procedure from one of the answers, and queries to demonstrate the issue.

Query 1: sample_table

-- Note: The "bad dash" row has char(150)

SELECT * FROM sample_table;

+-------------------+
|    DataColumn     |
+-------------------+
| test - good dash  |
| test – bad dash   |
+-------------------+

Query 2: The other answer by John shows the "bad dash" row containing char(150):

SELECT dbo.Find_Invalid_Chars(DataColumn) [Invalid Characters]
FROM sample_table
WHERE dbo.Find_Invalid_Chars(DataColumn) IS NOT NULL;

+----------------------+
|  Invalid Characters  |
+----------------------+
| test [150] bad dash  |
+----------------------+

Query 3: The accepted answer by Martin Smith returns no results:

SELECT DataColumn AS [Bad Data]
FROM sample_table
WHERE DataColumn LIKE '%[' + CHAR(127)+ '-' +CHAR(255)+']%' COLLATE Latin1_General_100_BIN2;

+------------+
| [Bad Data] |
+------------+

-- No rows returned.

Conclusion

Unfortunately, I often need to find characters within (or outside of) a range in databases I can't create stored procedures in. I'd really like to find a fix for the accepted answer or a simple script that wouldn't require creation of any objects (including temp tables).

Any suggestions? Thanks in advance.

EDIT 1: The solution cannot modify or add any objects or settings in the database. I'm looking for a self-contained query that will select rows with one or more characters in a range between two CHAR() numbers, regardless of the ASCII or Extended ASCII number supplied.

EDIT 2: The DataColumn can be in either VARCHAR or NVARCHAR. I have no control over this, so I'm hoping to figure out a self-contained query that works for both. The purpose of the query is to find characters in the source table/column that are not handled correctly by some software applications. The applications are interpreting the source correctly, but sometimes have issues with characters outside "standard" ranges, though the ranges vary by application.

  • why your Query 3 is not looking for char(150) ? Try WHERE DataColumn LIKE '%[' + CHAR(150)+']%' – Squirrel Oct 23 '19 at 4:03
  • Hi Fred. Can you please be more specific with what you are looking for. Do you just need to find rows that have 1 or more non-ASCII characters? Do you need to find the specific characters in each row that has them? Do you need to replace them? What do you consider to be "non-ASCII"? I only ask because the question you linked to erroneously identifies "CHAR(1) through CHAR(31)" as "non-ASCII" when those characters are in fact ASCII. Non-ASCII, would more correctly mean only characters values 128 - 255 (i.e. the Extended ASCII range). Also, what is the default collation of your DB? – Solomon Rutzky Oct 23 '19 at 6:14
  • @Squirrel Query 3 looks for a range between 127 and 255. – Fred Oct 23 '19 at 13:37
  • @Solomon Rutzky - The term Non-ASCII was from the original poster that I linked to. I am aware those characters are in fact ASCII. The query I was looking for would select rows with one or more characters in a range between two CHAR() numbers, regardless of the ASCII or Extended ASCII number supplied. – Fred Oct 23 '19 at 13:43
  • @Fred Ok, thanks for clarifying. Now that I understand better I will come up with something and update my answer with it. ALSO, converting string data into a particular code page (hence, VARCHAR) from either NVARCHAR or VARCHAR of a different code page may convert an invalid source character into a valid target character. For example, superscript "²" (not standard ASCII) sometimes converts to a regular decimal digit "2" (standard ASCII). The list of characters and what they convert to, if anything, is per code page. Which value should this query see: source or target? – Solomon Rutzky Oct 23 '19 at 16:13
4

Why is the accepted answer not working for char(150)?

Actually, it does. The problem is your test is bad / invalid. You test column, DataColumn, is using NVARCHAR instead of VARCHAR. The character itself works in both datatypes, but the behavior is different due to how it is being used in each case:

  • In the Find_Invalid_Chars() function (i.e. the "other" answer), the string is being converted back into VARCHAR since that is the datatype of the input parameter for that function. In this case it works as expected (although I believe it can be done much more efficiently than that loop, but that's for another time ;-)
  • In the LIKE query (i.e. the "accepted" answer), the expanded and concatenated result of '%[' + CHAR(127)+ '-' +CHAR(255)+']%' is actually converted into NVARCHAR since that is the datatype of the column it is being compared to (and NVARCHAR has a higher datatype precedence), hence that LIKE function is not behaving as expected: either the CHAR(255) character maps to a different code point, and/or the CHAR(150) character in the column itself maps to a different code point (the CHAR(127) character does not change as it's in the standard ASCII range). In either case, the conversion to NVARCHAR is causing the numerical value of the "En Dash" character ("–") to no longer be within that range. Meaning, the LIKE function is looking for values, y, between 127 and x (where x >= 128), and y for the "En Dash" character is now > x. Whereas in VARCHAR, x = 255 and y = 150.

The quick fix to see that it does work is simply to change the NVARCHAR datatype of the DataColumn column to be VARCHAR (yes, just remove the initial "N"), then re-build the schema, then execute, and the LIKE query will behave as expected.

The following might help explain why making the test column NVARCHAR caused the LIKE query to not match the row:

SELECT UNICODE(CHAR(127)) AS [CHAR(127)],
       UNICODE(CHAR(150)) AS [CHAR(150)],
       UNICODE(CHAR(255)) AS [CHAR(255)];

/*
CHAR(127)     CHAR(150)     CHAR(255)
127           8211          255
*/

As you can see in the results below the query, the "bad dash", which was CHAR(150) became NCHAR(8211) when stored in the NVARCHAR column. And, since that predicate is using a binary collation (generally the correct thing to do in this scenario), it was looking at the code points / values, not the characters. Hence, the LIKE clause was looking for characters with values between 127 and 255, and 8211 is typically not in that range ;-).

P.S. Please keep in mind that the function CHAR(150) can return different characters, or even NULL, based on the default collation of the database in which you execute that function. This is because VARCHAR data is based on code pages, and those are determined by the collation, and the collation being used when executing the CHAR() function is the default collation of the active / current database. This affects values 128 - 255. Values 0 - 127 will always return the same characters, regardless of collation, as those are the standard ASCII character set and are the same across all of the code pages supported in SQL Server (though not in all code pages in general).

P.P.S. ALSO, I just noticed a slight difference in logic between the function and the query (i.e. the two answers from the linked question): CHAR(127) is considered good / valid in the Find_Invalid_Chars() function, yet it's considered bad / invalid in the LIKE query. If it were me, I would consider CHAR(127) valid as it's part of the standard ASCII character set. But, you need to decide what you consider it. Just be aware of that difference in case you do need to adjust the LIKE syntax a little.


Given:

  1. The purpose of the query is to find characters in the source table/column that are not handled correctly by some software applications.

    and:

  2. The data can be in either VARCHAR or NVARCHAR.

I would say that:

  1. You don't want to convert NVARCHAR source data into VARCHAR as there might be "best fit" mappings that translate invalid source characters into valid characters, but one or more of your software applications might not use "best fit" mappings.

    SELECT NCHAR(178) AS [Unicode], -- Superscript 2 (U+00B2)
           CONVERT(VARCHAR(5), NCHAR(178)
                       COLLATE SQL_Latin1_General_CP1_CI_AS) AS [CodePage-1252],
           CONVERT(VARCHAR(5), NCHAR(178)
                       COLLATE Turkmen_100_CI_AS) AS [CodePage-1250]
    
    /*
    Unicode    CodePage-1252    CodePage-1250
    ²          ²                2
    */
    
  2. It will likely be more reliable to look for characters not in a specific "valid" range as opposed to those in a specific invalid range, especially when dealing with NVARCHAR which holds a lot more than 256 characters.
  3. You could get away with a single query if the "valid" range is always between values 0 and 127 (since those values are the same in both cases). But if you need to specify values above 127, then you will need one query for VARCHAR and one for NVARCHAR.

All that being said:

  • The following query returns rows containing at least one character that is not in the range of 0 - 127, for both VARCHAR and NVARCHAR. But, it only works with NVARCHAR columns for values above 127.

    SELECT *
    FROM   (VALUES (NCHAR(178)), (NCHAR(8211)), (N''), (NULL), (N'xy' + NCHAR(165)),
               (N'AA'), (N'mM' + NCHAR(999) + N'Nn'), (N'#!~()')) tmp(TestValue)
    WHERE  tmp.[TestValue] LIKE N'%[^' + NCHAR(0) + N'-' + NCHAR(127)
              + N']%' COLLATE Latin1_General_100_BIN2;
    
    /*
    TestValue
    ²
    –
    xy¥
    mMϧNn
    */
    
  • The following query also returns rows containing at least one character that is not in the range of 0 - 127, but only works for VARCHAR columns. However, it does allow using values between 128 and 255.

    SELECT *
    FROM   (VALUES (CHAR(178)), (CHAR(150)), (''), (NULL), ('AA'), ('#!~()'),
            ('xy' + CONVERT(VARCHAR(5), NCHAR(165) COLLATE Latin1_General_100_BIN2)),
            ('mM' + CONVERT(VARCHAR(5), NCHAR(199) COLLATE Latin1_General_100_BIN2) + 'Nn')
           ) tmp(TestValue)
    WHERE  tmp.[TestValue] LIKE '%[^' + CHAR(0) + '-' + CHAR(127)
              + ']%' COLLATE Latin1_General_100_BIN2;
    
    /*
    TestValue
    ²
    –
    xy¥
    mMÇNn
    */
    

Regarding:

The applications are interpreting the source correctly, but sometimes have issues with characters outside "standard" ranges, though the ranges vary by application.

  1. Not sure I understand how there can be "issues" with some characters if the app is interpreting the source data correctly, unless you meant that they "mostly" interpret the data correctly.
  2. The ranges varying by application sound like this might require more detailed investigation than can be done in a simple Q & A format such as this. This behavior can be due to them using different drivers to connect (ODBC / OLEDB / etc), what language they are written in, what assumptions they are making about the data they are getting, and so on. Some problems might be fixable with a configuration (no code change) of the app, some might be fixable only with a code change, etc.
|improve this answer|||||
  • Thanks for the detailed response, it was quite educational and much appreciated. Unfortunately, I can't use the provided solution as it would require modifying an object (table) in the database. I'm going to try playing around with converting NVARCHAR to VARCHAR in the query, but not sure that will resolve the issue. Also, I am not sure how to solve the issue of different collation in the database. Any other suggestions would be most welcome. – Fred Oct 23 '19 at 13:55
  • Hi @Fred . Ok. To be fair, there's no indication in the question that your column is actually NVARCHAR as everything is in terms of VARCHAR (string literals, referenced question only deals with VARCHAR, use of CHAR() instead of NCHAR(), etc). It certainly looked like the use of NVARCHAR in the SQL Fiddle demo wasn't exactly intentional. Your source data being NVARCHAR is a critical detail that needs to be added to the question. I'll update my answer soon to account for this. Converting to VARCHAR may or may not work, depending on the exact goal (will comment on question). – Solomon Rutzky Oct 23 '19 at 16:01
  • Hi @Soloman. Understood, I'll update the question. FYI - Whatever solution I find needs to work regardless of VARCHAR or NVARCHAR, as the databases I'm trying to evaluate could have either. Sorry if this is getting more complicated than you expected. Thanks for the assistance! – Fred Oct 23 '19 at 16:05
  • @Soloman Thanks for all the great detail. Your example using not in the range of 0 - 127 worked perfectly! Much appreciated, I learnt a lot. – Fred Oct 24 '19 at 3:01
  • 1
    @Fred You're welcome. Glad it helped. To be clear, both of the examples at the end find rows "not in the range of 0 - 127". I updated the wording on the 2nd one to make it clearer that it a) did the same "not in 0 - 127 range" as the first one, and b) only works for VARCHAR, and c) allows for using full 0 - 255 range for VARCHAR (which the first query can't do, at least not reliably). Take care.. – Solomon Rutzky Oct 24 '19 at 3:36

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.