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!

If you can add the `REGEXP` user defined function ([see here](https://stackoverflow.com/questions/5071601/how-do-i-use-regex-in-a-sqlite-query)), then you may use: `CHECK(myfield REGEXP '^[A-HJ-NPR-Z0-9]{17}$')` — Tim Biegeleisen, Oct 04 '20 at 03:56
@TimBiegeleisen That would be wonderful - and I have a working solution with `REGEXP` - but the confines of the question I'm trying to work out requires that I stick with `GLOB`. — AnagramDatagram, Oct 04 '20 at 04:11
You could look at the string function trim() and see if you can do something with that. — Gordon Royle, Oct 08 '20 at 02:36

forpas · Accepted Answer · 2020-10-04T09:50:30.523

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.

score 0 · Answer 2 · edited Oct 04 '20 at 06:36

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 Answers2