1

Issue

I've got a field intended for codified strings we use for typological classifications in organology.

I'd like that field to have a constraint that would prevent getting out of the standardised formatting, which is nnn.nnn.nn

--> as an example : 421.221.12.

However, I should also be able to enter partial strings, such as :

  • 421.-
  • 421.2--
  • 421.22-
  • 421.221.-
  • and so on (dashes -or -- are not mandatory but they are convenient to stress that the information is missing)

Thoughts

I found some cool stuff here, here and there and managed to come up with the following, but incomplete solution :

typology TEXT CHECK(
   typology LIKE '___.___.__'
OR typology LIKE '___.___._'
OR typology LIKE '___.___.'
OR typology LIKE '___.__'
OR typology LIKE '___._'
OR typology LIKE '___.'
)



► output when wrong entry:

Execution finished with errors.
Result: CHECK constraint failed: a LIKE '___.___.__'
At line 1:
insert into test2 (a) values ('wrong.ent.ry');

So that's quite alright but wierd stuff could still get through without any warnings such as :

INSERT INTO table1 (typology) VALUES
('421.111.12'),
('421.111...'),
('-_-.-_-.--'),
('..........'),
('got.you.!!');

Which is why I was thinking regex in order to specify that those _ need to be digits.

More thoughts

I figured I could also use either a very long list for a CHECK statement, e.g. :

CHECK(typology IN ('421','421.1','421.11',...,'421.23'))

But the list is like... reaaaaaaaly long (about 50-60 possibilities).

I was also thinking of perhaps disconnecting the parts and dividing it into two different fields (nnn is always 421 at the beginning) in order to reduce the length of the list, but it doesn't seem really convenient, e.g. :

typologyA INTEGER CHEK(typologyA in (1,11,111,112,12,121,122,13,14,141,142,2,21,211,212,22,221,222,23)),
typologyB INTEGER CHEK(typologyB in (1,11,12,2,21,22,3,31,311,312,32,4,41,42))

► This would be the actual lists of options if I needed to do it this way.

Etsaf
  • 83
  • 7
  • Did you checked [How do I use regex in a SQLite query](http://stackoverflow.com/questions/5071601/how-do-i-use-regex-in-a-sqlite-query) – Ptit Xav Sep 21 '21 at 16:12
  • @PtitXav yes I did, but that's also why I was wondering : the subject is about queries while my issue is about the `CHEK` statement ► do you think this add-on extends to those too? – Etsaf Sep 21 '21 at 17:47

0 Answers0