Valid charset check

Question

I need your help. One of our integrations is failing as we only certain charset

What i would like to do is run an adhoc query on the following column (FirstName) and flag any record which is not a valid character listed below

Table : Customer Column : FirstName

Characters allowed : ! " # $ % & ' ( ) * + , - . / 0 1 2 3 4 5 6 7 8 9 : ; < = > ? @ A B C D E F G H I J K L M N O P Q R S T U V W X Y Z [ \ ] ^ _ ` a b c d e f g h i j k l m n o p q r s t u v w x y z { | } ~
¡ ¢ £ ¤ ¥ ¦ § ¨ © ª « ¬ ­ ® ¯ ° ± ² ³ ´ µ ¶ · ¸ ¹ º » ¼ ½ ¾ ¿ À Á Â Ã Ä Å Æ Ç È É Ê Ë Ì Í Î Ï Ð Ñ Ò Ó Ô Õ Ö × Ø Ù Ú Û Ü Ý Þ ß à á â ã ä å æ ç è é ê ë ì í î ï ð ñ ò ó ô õ ö ÷ ø ù ú û ü ý þ ÿ

Answer 1

You can use LIKE to test if a string has any characters not in a list, using the [^] wildcard pattern.

Some characters have a special meaning when used with LIKE, for example % and [. These need to be escaped to work as expected.

You seem to be using all of the printable characters in the 8-bit ASCII range in your test string, so I'm using the Unicode character for escape (␛) below:

CREATE TABLE dbo.Customer
(
    FirstName nvarchar(50) NOT NULL
);

INSERT dbo.Customer 
    (FirstName)
VALUES 
    (N'Paul'),
    (N'[%_^]'),
    (N'Māori')


-- Line breaks added for clarity
DECLARE @Allowed nvarchar(4000) = N'
    !"#$␛%&''()*+,␛-./0123456789:;<=>?
    @ABCDEFGHIJKLMNOPQRSTUVWXYZ␛[\␛]␛^␛_
    `abcdefghijklmnopqrstuvwxyz{|}~
    ¡¢£¤¥¦§¨©ª«¬­®¯°±²³´µ¶·¸¹º»¼½¾¿À
    ÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖ×ØÙÚÛÜÝÞßà
    áâãäåæçèéêëìíîïðñòóôõö÷øùúûüýþÿ';

SELECT C.FirstName 
FROM dbo.Customer AS C
WHERE C.FirstName LIKE N'%[^' + @Allowed + ']%' ESCAPE N'␛';

Result:

FirstName
Māori