Using GLOB to match each character in an SQLite TEXT field

Question

I have an TEXT field in an SQLite table that is required to be exactly 17 characters long. Let's call it myfield.
For any arbitrary row in this table, how can we use SQLite's GLOB operator within a CHECK bracket to match each character in the myfield column entry (and, if possible, enforce the 17 character constraint)?
Every character in this entry can be any digit 0 - 9, or any uppercase letter excluding I, O, and Q. Also, the 9th character of the myfield entry can only be a digit 0 - 9 or the letter X, but I'm still trying to get the previous conditions first.
What have I tried?

CHECK(myfield GLOB '[A-HJ-NPR-Z0-9]{17}') - This doesn't work, and I think it's because GLOB doesn't support the curly bracket notation - correct me if I'm wrong.
CHECK(length(myfield) == 17 AND myfield GLOB '[A-HJ-NPR-Z0-9]') - Also doesn't work, presumably because the second check condition only matches a single character, contradicting the first.
I'm convinced there's a simpler solution than setting up 17 check conditions for each character in the string!

forpas · Answer

In SQLite there is a trick to emulate a function like MySql's REPEAT() which returns a string repeated n times.
So by:
REPLACE(HEX(ZEROBLOB(8)), '00', '[A-HJ-NPR-Z0-9]')

you get the string '[A-HJ-NPR-Z0-9]' repeated 8 times:
[A-HJ-NPR-Z0-9][A-HJ-NPR-Z0-9][A-HJ-NPR-Z0-9][A-HJ-NPR-Z0-9][A-HJ-NPR-Z0-9][A-HJ-NPR-Z0-9][A-HJ-NPR-Z0-9][A-HJ-NPR-Z0-9]

You can use this trick to construct (by concatenations) the string that you need after GLOB in your CHECK constraint:
CHECK(myfield GLOB 
                REPLACE(HEX(ZEROBLOB(8)), '00', '[A-HJ-NPR-Z0-9]') || 
                '[X0-9]' || 
                REPLACE(HEX(ZEROBLOB(8)), '00', '[A-HJ-NPR-Z0-9]')
)

See a simplified demo.

Beston · Answer

Simply copy and paste it for 17 times
CHECK (myfield GLOB "[A-HJ-NPR-Z0-9][A-HJ-NPR-Z0-9][A-HJ-NPR-Z0-9][A-HJ-NPR-Z0-9][A-HJ-NPR-Z0-9][A-HJ-NPR-Z0-9][A-HJ-NPR-Z0-9][A-HJ-NPR-Z0-9][X0-9][A-HJ-NPR-Z0-9][A-HJ-NPR-Z0-9][A-HJ-NPR-Z0-9][A-HJ-NPR-Z0-9][A-HJ-NPR-Z0-9][A-HJ-NPR-Z0-9][A-HJ-NPR-Z0-9][A-HJ-NPR-Z0-9]")
And at the 9th element, just key in X0-9, so "the 9th character of the myfield entry can only be a digit 0 - 9 or the letter X" can be checked too
By doing this, you can also enforce the 17 characters constraint

Using GLOB to match each character in an SQLite TEXT field

2 Answers

Add your own answers!

Ask a Question