[FIXED] like statement not working in sql check statement in sqlite3

Issue

I'm working on a project and I need to create a table with phone number, I want to check that all the characters are digits by using the regex '^[0-9]{10}$' in a check statement while creating the table, but when I enter any phone number like '0522233355' I got error in check statement, which contains the regex, I'm attaching my code:

def create_table():
    customers_table = """
    create table if not exists Customers(
        phone_number nvarchar(10) primary key,
        first_name nvarchar(15),
        last_name nvarchar(15),
        city nvarchar(20),
        address nvarchar(50),
        backup_phone nvarchar(10),
        password nvarchar(20),
        check (length(phone_number) == 10),
        check (phone_number not like backup_phone),
        check (phone_number like '^[0-9]{10}$'),
        check (length(password) >= 8 and length(password) <= 20),
        check (password like '^.{8,20}$')
     )
    """
    
    products_table = """
    create table if not exists Products(
        id_number int auto_increment primary key,
        name nvarchar(50),
        img_path nvarchar(100),
        price nvarchar(10),
        description nvarchar(200),
        publish_year nvarchar(20),
        author_name nvarchar(20)
    )
    """
    
    with sqlite3.connect("Shopping.db") as connection:
        cursor = connection.cursor()
        try:
            cursor.execute(customers_table)
            cursor.execute(products_table)
            connection.commit()
        except Exception as e:
            print(e)
            connection.rollback()

I tried to used 'regexp' but the interpreter doesn't recognize it while the sql console use it with ease. I tried: check (phone_number like '0522233355'), and it worked. I tried: check (phone_number like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'), not working. I tried different phone numbers, but the same result. that's what I have tried.

Solution

Here is how you could amend the usages of LIKE

    create table if not exists Customers(
        phone_number nvarchar(10) primary key,
        first_name nvarchar(15),
        last_name nvarchar(15),
        city nvarchar(20),
        address nvarchar(50),
        backup_phone nvarchar(10),
        password nvarchar(20),
        check (length(phone_number) == 10),
        check (phone_number != backup_phone)
        check (phone_number GLOB '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
        check (length(password) >= 8 and length(password) <= 20)
     )

Given that you are using Python, I would suggest checking the regex with a user defined function in Python, as SQLite is limited in regex capabilities, supporting {n} could be done using tricks like this one

For example,

import sqlite3
import re

# Check if a string contains exactly 10 digits
def regex_check(s):
    return re.match(r'^[0-9]{10}$', s) is not None

conn = sqlite3.connect('DATABASE_NAME.db')
conn.create_function("REGEX_CHECK", 1, regex_check)

# Now REGEX_CHECK is usable in SQL, for example
conn.execute("""
    create table if not exists Customers(
        phone_number nvarchar(10) primary key,
        -- other fields
        check (REGEX_CHECK(phone_number))
        -- other constraints
    )
""")

Answered By - weshouman