[SOLVED] SQLite, ASCII Characters 0x20 to 0x7E Check Constraint

SQLite, ASCII Characters 0x20 to 0x7E Check Constraint

I have the following table where I'm trying to limit the column "name" to ASCII characters ranging from 0x20 to 0x7E (all printable ASCII characters). Why is my check constraint not working?

CREATE TABLE test
( 
    id INTEGER NOT NULL,
    name TEXT NOT NULL CHECK(name NOT GLOB '*[^\x20-\x7E]*'),
    PRIMARY KEY(id)
)

For instance:

INSERT INTO test (name) VALUES("Tom");

Expected: Should add a touple with name: "Tom" to the table (as the inserted name only consists of printable ASCII characters). Result: CHECK constaint failed: test

Solution

Backslashes are not special in SQL strings. '\x20' is 4 individual characters, not an escaped single character, for example. So your GLOB pattern always matches (Because T is not a character in the set), and the NOT makes it, and thus the constraint, fail.