[SOLVED] Runtime error while execute sql file by sqlite3 "unclosed ["

Runtime error while execute sql file by sqlite3 "unclosed ["

I have sql file generated by DB browser:

BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "NB" (
    "phone" TEXT CHECK("phone" REGEXP '^(\+\d\d)?[\s-]?')
);
INSERT INTO "NB" VALUES ('+33 123 456 789');

When I tried to execute it with sqlite3: sqlite3 nb.db <NB.sql I get an error: Runtime error near line 5: unclosed '['.

What is wrong and why I get this error?

Solution

SQLite's built-in regexp() ist very minimal - for example it does not support backslash escapes (\+, \d, \s) or quantifiers {n} ... in your use case - thx for the clarification to @user2722968

Solution 1

It would work with this check - it only contains basic validation, but also comes with some downsides:

CREATE TABLE IF NOT EXISTS "NB" (
    phone TEXT CHECK(
        phone GLOB '+[0-9][0-9] [0-9][0-9][0-9] [0-9][0-9][0-9] [0-9][0-9][0-9]'
    )
);

Pros: simple to use
Cons: It only matches exactly the defined pattern e.g.:

Works: +33 123 456 789, +22 123 456 789, ...
Does not work: +333 123 456 789, +333 123456789, ...

Solution 2 (Recommended)

You can use your original REGEXP check - but therefor you need to load the regexp.dll

You can download the files from this repository: https://github.com/nalgeon/sqlean
I have just used the "sqlean-win-x64" - download the artifacts, extract and load the dll file:

.load ./../sqlean-win-x64/regexp.dll

CREATE TABLE IF NOT EXISTS "NB" (
    "phone" TEXT CHECK("phone" REGEXP '^(\+\d\d)?[\s-]?')
);

If you see the following error, keep on reading:
Parse error: unsafe use of REGEXP()

The following line resolved that issue, if it occures

PRAGMA trusted_schema = ON;

It tells SQLite to allow extension-provided functions (like your PCRE-powered REGEXP) inside schema definitions (CHECKs, generated columns, etc.) without disabling its other safety checks.