What exactly “did not work” in the spreadsheet file I attached yesterday? It does the conditional formatting right. The invalid entries are displayed in red font (style “Warning”). If that “does not work” for you, I would think that you run some 6.x version of LibreOffice.
The screenshot attached to the document shows how I did the trick. It is a mixed reference to the cell in column A and an absolute reference to the regex. Keeping the regex in a separate cell makes debugging a lot easiser.
It’s not working for me.
I think it’s not acceptable to mess up with such stuff to impose a validation condition on a value…
It was very kind of you, but I find libreoffice environment absolutely confusing, error prone, undebuggable, and I’d say irrational. The fact function names change based on the language of the user is completely mad, too, according to me…
I’m giving up. Thanks for your support.
“It’s not working” is not a valid problem description". The uploaded file formats the strings in column A correctly. Column B shows the results of the REGEX function. Column E shows the result of the test.
Version: 7.2.4.1 / LibreOffice Community
Build ID: 27d75539669ac387bb498e35313b970b7fe9c4f9
CPU threads: 4; OS: Linux 5.4; UI render: default; VCL: x11
Locale: de-DE (de_DE.UTF-8); UI: en-US
Calc: threaded
You are right, I also perceive it as madness …
But there is good news: the LibreOffice developers have provided a “pill for spoiled nerves”. It’s in Tools - Options - LibreOffice Calc - Formula.
Storing strings in an arithmetic calculator is a foolish idea anyway (extremely popular, though).
Open the attached database and embedded input form.
t72586.odb (12.4 KB)
Spreadsheets are used, among other things, for the exchange of information between people (and computers). Dump your databases? 
Data storage (list keeping) is by far easier to maintain with databases. I dump the result of a database query into some document (mostly Writer, sometimes Calc) when I want to exchange documents.
In this particular case I do not enter invalid strings instead of detecting them afterwards. Same with csv. I avoid importing invalid record sets instead of trying to fix wrongly imported data.
@Villeroy I cannot think of how to implement validation against three patterns using an input mask. Yes, Italian lawmakers put a pig on the programmers when they introduced three different formats for one identifier. But how is such a problem solved in Base? Any ideas?
To be honest, it makes some sense, since the 3 different formats correspond to different entities (persons, italian companies, foreign companies).
However, JohnSUN suggestion is working for me now:
AND(
ISERROR(REGEX(A3;"^[0-9]{11}$"));
ISERROR(REGEX(A3;"^[A-Za-z]{6}[0-9]{2}[A-Za-z]{1}[0-9]{2}[A-Za-z]{1}[0-9A-Za-z]{3}[A-Za-z]{1}$"));
ISERROR(REGEX(A3;"^[A-Za-z]{6}[0-9LMNPQRSTUV]{2}[A-Za-z]{1}[0-9LMNPQRSTUV]{2}[A-Za-z]{1}[0-9LMNPQRSTUV]{3}[A-Za-z]{1}$"))
)
As I said two days ago if you think about the problem long enough, you can come up with a shorter expression - @erAck did exactly that (for some reason I didn’t succeed the first time, I had to combine three different REGEX())
Why bothering? Perhaps it’s better to keep the 3 expressions separated…
3 pattern fields bound to the same table column.
1 Like
It may be better to have 3 columns for 3 pattern types and a CHECK constraint making sure that only one of the 3 is used in a record. However, there is no pattern for character class [0-9LMNPQRSTUV]. This could be solved with a database that supports regular expressions such as HSQL 2.x and another CHECK constraint checking against the regex.
In any case, the solution will not be easy. This is not the only time that people with … uh-uh … poetic minds make life difficult for programmers and computers.
Using the right database engine with regex support, it was a matter of a 15 minutes.
Extract Regex_Check to a trusted directory. The archive contains a database document with a macro, a driver directory with one driver file (hsql 2.4.1) and a database directory. The macro is an auto-installer connecting the document with the driver and the database. Normal operatiion does not require any macro code. The macro is triggered on document open and adjusts the installation paths when the package has moved to another directory.
The database engine checks if the string entered into column “CODE” matches any of the given 2 regular expressions or if you entered a numeric string of 11 digits but not both. Any record with an invalid code will be rejected.
The input form has 2 pattern fields, one for alphanumeric code and the other one for the numeric variant. You can enter only one of the two.
EDIT: I replaced the initial link Regex_Check.zip with a better version of the same.
So, if another identifier format appears, it is enough to add a new OR REGEXP_MATCHES() and execute ALTER TABLE? Elegantly…
You have to drop the constraint and replace it with the modified one. Mostly copy and paste. You find the all the database definition code in the database/script file.
AND the new constraint must not conflict with existing data.
I found a much better solution:
alter table TBL add column NCODE CHAR(11);
update TBL set NCODE= CODE WHERE Length(CODE)=11;
alter table TBL alter column CODE SET NULL;
update TBL set CODE=Null WHERE Length(CODE)=11;
alter table TBL drop constraint REGEX_CHECK
alter table TBL alter column CODE CHAR(16);
ALTER TABLE "TBL" ADD CONSTRAINT "REGEX_CHECK" CHECK(
REGEXP_MATCHES(PUBLIC."TBL"."CODE",'^[A-Za-z]{6}[0-9]{2}[A-Za-z]{1}[0-9]{2}[A-Za-z]{1}[0-9A-Za-z]{3}[A-Za-z]{1}$')
OR REGEXP_MATCHES(PUBLIC."TBL"."CODE",'^[A-Za-z]{6}[0-9LMNPQRSTUV]{2}[A-Za-z]{1}[0-9LMNPQRSTUV]{2}[A-Za-z]{1}[0-9LMNPQRSTUV]{3}[A-Za-z]{1}$')
OR PUBLIC."TBL"."CODE" IS NULL
)
ALTER TABLE "TBL" ADD CONSTRAINT "DIGIT11_CHECK" CHECK(
REGEXP_MATCHES(PUBLIC."TBL"."NCODE",'^[0-9]{11}$')
OR PUBLIC."TBL"."NCODE" IS NULL
)
ALTER TABLE "TBL" ADD CONSTRAINT "ALTERNATE_CHECK" CHECK(
PUBLIC."TBL"."CODE" IS NULL AND NOT PUBLIC."TBL"."NCODE" IS NULL
OR NOT PUBLIC."TBL"."CODE" IS NULL AND PUBLIC."TBL"."NCODE" IS NULL
)
Now you have CODE (16 alphanumeric characters) and NCODE(11 digits).
You can enter a valid alpha-numeric string into CODE or a numeric string into NCODE but not both.
The link to the new version resulting from the above SQL script is Regex_Check.odb
Regular expressions have () capturing parentheses grouping and | OR alternation operator, refined to use the slightly more efficient (?:...) non-capturing parentheses grouping, gives one expression
^(?:[0-9]{11})|(?:[A-Za-z]{6}[0-9]{2}[A-Za-z]{1}[0-9]{2}[A-Za-z]{1}[0-9A-Za-z]{3}[A-Za-z]{1})|(?:[A-Za-z]{6}[0-9LMNPQRSTUV]{2}[A-Za-z]{1}[0-9LMNPQRSTUV]{2}[A-Za-z]{1}[0-9LMNPQRSTUV]{3}[A-Za-z]{1})$
1 Like