AnswerBun.com

Using GLOB to match each character in an SQLite TEXT field

Stack Overflow Asked by AnagramDatagram on December 21, 2020

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?

  1. 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.
  2. 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.
  3. I’m convinced there’s a simpler solution than setting up 17 check conditions for each character in the string!

2 Answers

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.

Answered by forpas on December 21, 2020

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

Answered by Beston on December 21, 2020

Add your own answers!

Related Questions

Cannot append option for select form

1  Asked on November 20, 2021 by nguyn-v

   

Constructors with the same primitive type as parameter

3  Asked on November 18, 2021 by arjun-singh

   

Python regex for numbers between 0-63

3  Asked on November 18, 2021 by perplexityy

   

How can I retrieve many values of a same field in a GET request

2  Asked on November 18, 2021 by user2424634

       

Can i know why my while loop is not working?

0  Asked on November 18, 2021 by niranjan-kumar

 

Moment JS days difference issue

1  Asked on November 18, 2021

   

How to update the value of a prop upon a state change in React

2  Asked on November 18, 2021 by alopez02

 

reload div without refresh page

1  Asked on November 18, 2021 by kemar-aim

         

Convert heirachy to dict of lists

0  Asked on November 18, 2021 by himabindu

       

Ask a Question

Get help from others!

© 2022 AnswerBun.com. All rights reserved. Sites we Love: PCI Database, MenuIva, UKBizDB, Menu Kuliner, Sharing RPP