Revisions to How to check for Non-Ascii Characters

make code readable without having to scroll sideways

Source Link

edit approved Sep 22, 2022 at 16:50

1.6k
1
10
21

DECLARE @WhiteListedCharacters NVARCHAR(1000 )  
= ' !"#$%&''()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz@ABCDEFGHIJKLMNOPQRSTUVWXYZ'
+ '[\]^_`abcdefghijklmnopqrstuvwxyz{|}~'

SELECT text,
        REPLACE(
            TRANSLATE(text, 
            text,
            @WhiteListedCharacters COLLATE Latin1_General_100_BIN2, 
            REPLICATE(
            REPLICATE(    LEFT(@WhiteListedCharacters,1),
                LEN(@WhiteListedCharacters))), 
        LEFT(@WhiteListedCharacters,1), 
        '') AS BadChars
FROM   sys.messages
WHERE  language_id = 1038

DECLARE @WhiteListedCharacters NVARCHAR(1000 )  
= ' !"#$%&''()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz@ABCDEFGHIJKLMNOPQRSTUVWXYZ'
+ '[\]^_`abcdefghijklmnopqrstuvwxyz{|}~'

SELECT text,
    BadChars,
    BadChars,Cleaned = REPLACE(
        Cleaned = REPLACE(        TRANSLATE(
                      text  COLLATE Latin1_General_100_BIN2,
                      BadChars,
                      REPLICATE(N'ψ', LEN(BadChars))
                      ),
              N'ψ', N'')
FROM   sys.messages
CROSS APPLY
(
SELECT REPLACE(
        REPLACE   TRANSLATE(
            TRANSLATE(   text, 
               @WhiteListedCharacters COLLATE Latin1_General_100_BIN2,
       @WhiteListedCharacters COLLATE Latin1_General_100_BIN2, 
      REPLICATE(
                   REPLICATE(LEFT(@WhiteListedCharacters,1),
                   LEN(@WhiteListedCharacters + '-') - 1)), 
               LEFT(@WhiteListedCharacters,1), 
        '') AS BadChars
) ca
WHERE  language_id = 1038

DECLARE @WhiteListedCharacters NVARCHAR(1000 ) = ' !"#$%&''()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~'

SELECT text,
        REPLACE(
            TRANSLATE(text, 
                        @WhiteListedCharacters COLLATE Latin1_General_100_BIN2, 
                        REPLICATE(LEFT(@WhiteListedCharacters,1), LEN(@WhiteListedCharacters))), 
        LEFT(@WhiteListedCharacters,1), 
        '') AS BadChars
FROM   sys.messages
WHERE  language_id = 1038

DECLARE @WhiteListedCharacters NVARCHAR(1000 ) = ' !"#$%&''()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~'

SELECT text,
        BadChars,
        Cleaned = REPLACE(TRANSLATE(text  COLLATE Latin1_General_100_BIN2, BadChars, REPLICATE(N'ψ', LEN(BadChars))), N'ψ', N'')
FROM   sys.messages
CROSS APPLY
(
SELECT 
        REPLACE(
            TRANSLATE(text, 
                        @WhiteListedCharacters COLLATE Latin1_General_100_BIN2, 
                         REPLICATE(LEFT(@WhiteListedCharacters,1), LEN(@WhiteListedCharacters + '-') - 1)), 
        LEFT(@WhiteListedCharacters,1), 
        '') AS BadChars
) ca
WHERE  language_id = 1038

DECLARE @WhiteListedCharacters NVARCHAR(1000) 
= ' !"#$%&''()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ'
+ '[\]^_`abcdefghijklmnopqrstuvwxyz{|}~'

SELECT text,
    REPLACE(
        TRANSLATE(
            text,
            @WhiteListedCharacters COLLATE Latin1_General_100_BIN2, 
            REPLICATE(
                LEFT(@WhiteListedCharacters,1),
                LEN(@WhiteListedCharacters))), 
        LEFT(@WhiteListedCharacters,1), 
        '') AS BadChars
FROM   sys.messages
WHERE  language_id = 1038

DECLARE @WhiteListedCharacters NVARCHAR(1000) 
= ' !"#$%&''()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ'
+ '[\]^_`abcdefghijklmnopqrstuvwxyz{|}~'

SELECT text,
    BadChars,
    Cleaned = REPLACE(
                  TRANSLATE(
                      text  COLLATE Latin1_General_100_BIN2,
                      BadChars,
                      REPLICATE(N'ψ', LEN(BadChars))
                      ),
              N'ψ', N'')
FROM sys.messages
CROSS APPLY
(
SELECT REPLACE(
           TRANSLATE(
               text,
               @WhiteListedCharacters COLLATE Latin1_General_100_BIN2,
               REPLICATE(
                   LEFT(@WhiteListedCharacters,1),
                   LEN(@WhiteListedCharacters + '-') - 1)),
               LEFT(@WhiteListedCharacters,1), 
        '') AS BadChars
) ca
WHERE language_id = 1038

Does work fine. I was confusing myself

Source Link

edited Dec 17, 2021 at 16:17

Martin Smith

86.5k
15
252
342

Ranges in the pattern syntax use the sorting rules of your collation.

Use a binary collate clause so the range is ordered by character code.

(I also changed it to LIKE as I find that more obvious than PATINDEX > 0)

SELECT *
FROM mbrnotes
WHERE LINE_TEXT COLLATE Latin1_General_100_BIN2 
     LIKE '%[' + CHAR(0)+ '-' +CHAR(31) + CHAR(127)+ '-' +CHAR(255)+']%'

If you actually want to see the offending characters and you are on a version with the TRANSLATE function you can use something like the below

DECLARE @WhiteListedCharacters NVARCHAR(1000 ) = ' !"#$%&''()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~'

SELECT text,
        REPLACE(
            TRANSLATE(text, 
                        @WhiteListedCharacters COLLATE Latin1_General_100_BIN2, 
                        REPLICATE(' 'LEFT(@WhiteListedCharacters,1), LEN(@WhiteListedCharacters))), 
        ' 'LEFT(@WhiteListedCharacters,1), 
        '') AS BadChars
FROM   sys.messages
WHERE  language_id = 1038

The above assumes that space is a "good" character. It ought to be possible to replace that with LEFT(@WhiteListedCharacters,1) to remove that assumption but in reality that did not work as expected on my local SQL Server 2019.

You can then use that result in a second call to TRANSLATE to preserve only the "good" characters.

DECLARE @WhiteListedCharacters NVARCHAR(1000 ) = ' !"#$%&''()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~'

SELECT text,
        BadChars,
        Cleaned = REPLACE(TRANSLATE(text  COLLATE Latin1_General_100_BIN2, BadChars, REPLICATE(N'ψ', LEN(BadChars))), N'ψ', N'')
FROM   sys.messages
CROSS APPLY
(
SELECT 
        REPLACE(
            TRANSLATE(text, 
                        @WhiteListedCharacters COLLATE Latin1_General_100_BIN2, 
                        REPLICATE(' 'LEFT(@WhiteListedCharacters,1), LEN(@WhiteListedCharacters + '-') - 1)), 
        ' 'LEFT(@WhiteListedCharacters,1), 
        '') AS BadChars
) ca
WHERE  language_id = 1038

Ranges in the pattern syntax use the sorting rules of your collation.

Use a binary collate clause so the range is ordered by character code.

(I also changed it to LIKE as I find that more obvious than PATINDEX > 0)

SELECT *
FROM mbrnotes
WHERE LINE_TEXT COLLATE Latin1_General_100_BIN2 
     LIKE '%[' + CHAR(0)+ '-' +CHAR(31) + CHAR(127)+ '-' +CHAR(255)+']%'

If you actually want to see the offending characters and you are on a version with the TRANSLATE function you can use something like the below

DECLARE @WhiteListedCharacters NVARCHAR(1000 ) = '!"#$%&''()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~'

SELECT text,
       REPLACE(
            TRANSLATE(text, 
                      @WhiteListedCharacters COLLATE Latin1_General_100_BIN2, 
                      REPLICATE(' ', LEN(@WhiteListedCharacters))), 
        ' ', 
        '') AS BadChars
FROM   sys.messages
WHERE  language_id = 1038

The above assumes that space is a "good" character. It ought to be possible to replace that with LEFT(@WhiteListedCharacters,1) to remove that assumption but in reality that did not work as expected on my local SQL Server 2019.

You can then use that result in a second call to TRANSLATE to preserve only the "good" characters.

DECLARE @WhiteListedCharacters NVARCHAR(1000 ) = '!"#$%&''()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~'

SELECT text,
       BadChars,
       Cleaned = REPLACE(TRANSLATE(text  COLLATE Latin1_General_100_BIN2, BadChars, REPLICATE(N'ψ', LEN(BadChars))), N'ψ', N'')
FROM   sys.messages
CROSS APPLY
(
SELECT 
       REPLACE(
            TRANSLATE(text, 
                      @WhiteListedCharacters COLLATE Latin1_General_100_BIN2, 
                      REPLICATE(' ', LEN(@WhiteListedCharacters + '-') - 1)), 
        ' ', 
        '') AS BadChars
) ca
WHERE  language_id = 1038

Ranges in the pattern syntax use the sorting rules of your collation.

Use a binary collate clause so the range is ordered by character code.

(I also changed it to LIKE as I find that more obvious than PATINDEX > 0)

SELECT *
FROM mbrnotes
WHERE LINE_TEXT COLLATE Latin1_General_100_BIN2 
     LIKE '%[' + CHAR(0)+ '-' +CHAR(31) + CHAR(127)+ '-' +CHAR(255)+']%'

If you actually want to see the offending characters and you are on a version with the TRANSLATE function you can use something like the below

DECLARE @WhiteListedCharacters NVARCHAR(1000 ) = ' !"#$%&''()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~'

SELECT text,
        REPLACE(
            TRANSLATE(text, 
                        @WhiteListedCharacters COLLATE Latin1_General_100_BIN2, 
                        REPLICATE(LEFT(@WhiteListedCharacters,1), LEN(@WhiteListedCharacters))), 
        LEFT(@WhiteListedCharacters,1), 
        '') AS BadChars
FROM   sys.messages
WHERE  language_id = 1038

You can then use that result in a second call to TRANSLATE to preserve only the "good" characters.

DECLARE @WhiteListedCharacters NVARCHAR(1000 ) = ' !"#$%&''()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~'

SELECT text,
        BadChars,
        Cleaned = REPLACE(TRANSLATE(text  COLLATE Latin1_General_100_BIN2, BadChars, REPLICATE(N'ψ', LEN(BadChars))), N'ψ', N'')
FROM   sys.messages
CROSS APPLY
(
SELECT 
        REPLACE(
            TRANSLATE(text, 
                        @WhiteListedCharacters COLLATE Latin1_General_100_BIN2, 
                        REPLICATE(LEFT(@WhiteListedCharacters,1), LEN(@WhiteListedCharacters + '-') - 1)), 
        LEFT(@WhiteListedCharacters,1), 
        '') AS BadChars
) ca
WHERE  language_id = 1038

added 1625 characters in body

Source Link

edited Dec 17, 2021 at 12:08

Martin Smith

86.5k
15
252
342

Ranges in the pattern syntax use the sorting rules of your collation.

Use a binary collate clause so the range is ordered by character code.

(I also changed it to LIKE as I find that more obvious than PATINDEX > 0)

SELECT * 
FROM mbrnotes
WHERE LINE_TEXT COLLATE Latin1_General_100_BIN2 
FROM mbrnotes    LIKE '%[' 
WHERE+ LINE_TEXTCHAR(0)+ LIKE'-' '%['+CHAR(31) + CHAR(127)+ '-' +CHAR(255)+']%'

If you actually want to see the offending characters and you are on a version with the TRANSLATE function you can use something like the below

DECLARE @WhiteListedCharacters NVARCHAR(1000 ) = '!"#$%&''()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~'

SELECT text,
       REPLACE(
            TRANSLATE(text, 
                      @WhiteListedCharacters COLLATE Latin1_General_100_BIN2, 
                      REPLICATE(' ', LEN(@WhiteListedCharacters))), 
        ' ', 
        '') AS BadChars
FROM   sys.messages
WHERE  language_id = 1038

The above assumes that space is a "good" character. It ought to be possible to replace that with LEFT(@WhiteListedCharacters,1) to remove that assumption but in reality that did not work as expected on my local SQL Server 2019.

You can then use that result in a second call to TRANSLATE to preserve only the "good" characters.

DECLARE @WhiteListedCharacters NVARCHAR(1000 ) = '!"#$%&''()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~'

SELECT text,
       BadChars,
       Cleaned = REPLACE(TRANSLATE(text  COLLATE Latin1_General_100_BIN2, BadChars, REPLICATE(N'ψ', LEN(BadChars))), N'ψ', N'')
FROM   sys.messages
CROSS APPLY
(
SELECT 
       REPLACE(
            TRANSLATE(text, 
                      @WhiteListedCharacters COLLATE Latin1_General_100_BIN2, 
                      REPLICATE(' ', LEN(@WhiteListedCharacters + '-') - 1)), 
        ' ', 
        '') AS BadChars
) ca
WHERE  language_id = 1038

Ranges in the pattern syntax use the sorting rules of your collation.

Use a binary collate clause so the range is ordered by character code.

(I also changed it to LIKE as I find that more obvious than PATINDEX > 0)

SELECT *      
FROM mbrnotes      
WHERE LINE_TEXT LIKE '%[' + CHAR(127)+ '-' +CHAR(255)+']%'  COLLATE Latin1_General_100_BIN2

Ranges in the pattern syntax use the sorting rules of your collation.

Use a binary collate clause so the range is ordered by character code.

(I also changed it to LIKE as I find that more obvious than PATINDEX > 0)

SELECT *
FROM mbrnotes
WHERE LINE_TEXT COLLATE Latin1_General_100_BIN2 
     LIKE '%[' + CHAR(0)+ '-' +CHAR(31) + CHAR(127)+ '-' +CHAR(255)+']%'

If you actually want to see the offending characters and you are on a version with the TRANSLATE function you can use something like the below

DECLARE @WhiteListedCharacters NVARCHAR(1000 ) = '!"#$%&''()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~'

SELECT text,
       REPLACE(
            TRANSLATE(text, 
                      @WhiteListedCharacters COLLATE Latin1_General_100_BIN2, 
                      REPLICATE(' ', LEN(@WhiteListedCharacters))), 
        ' ', 
        '') AS BadChars
FROM   sys.messages
WHERE  language_id = 1038

The above assumes that space is a "good" character. It ought to be possible to replace that with LEFT(@WhiteListedCharacters,1) to remove that assumption but in reality that did not work as expected on my local SQL Server 2019.

You can then use that result in a second call to TRANSLATE to preserve only the "good" characters.

DECLARE @WhiteListedCharacters NVARCHAR(1000 ) = '!"#$%&''()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~'

SELECT text,
       BadChars,
       Cleaned = REPLACE(TRANSLATE(text  COLLATE Latin1_General_100_BIN2, BadChars, REPLICATE(N'ψ', LEN(BadChars))), N'ψ', N'')
FROM   sys.messages
CROSS APPLY
(
SELECT 
       REPLACE(
            TRANSLATE(text, 
                      @WhiteListedCharacters COLLATE Latin1_General_100_BIN2, 
                      REPLICATE(' ', LEN(@WhiteListedCharacters + '-') - 1)), 
        ' ', 
        '') AS BadChars
) ca
WHERE  language_id = 1038

Source Link

created May 12, 2016 at 14:11

Martin Smith

86.5k
15
252
342

Loading

Stack Exchange Network

Return to Answer