SQLite中如何校验VARCHAR字段ex1的指定格式要求？

Got it, let's work through this constraint problem together. You already have the suffix check sorted, so we'll build the prefix number validation right into the same CHECK clause.

Full CREATE TABLE Statement #

CREATE TABLE tablename (
    ex1 VARCHAR NOT NULL CHECK (
        -- Match both lowercase t and uppercase T at the end
        LOWER(ex1) LIKE '%t'
        -- Ensure the prefix only contains digits
        AND SUBSTR(ex1, 1, LENGTH(ex1)-1) GLOB '[0-9]*'
        -- Check the numeric prefix falls between 1 and 30
        AND CAST(SUBSTR(ex1, 1, LENGTH(ex1)-1) AS INTEGER) BETWEEN 1 AND 30
    )
);

Breakdown of Each Constraint Part: #

• LOWER(ex1) LIKE '%t': Converts the entire string to lowercase first, so we don't have to write separate checks for t and T—cleaner and more concise.
• SUBSTR(ex1, 1, LENGTH(ex1)-1) GLOB '[0-9]*': Pulls out all characters except the last one, then uses SQLite's GLOB to confirm this prefix is made up entirely of digits. This blocks invalid entries like abcT or 12aT.
• CAST(...) AS INTEGER) BETWEEN 1 AND 30: Turns the digit-only prefix into an integer, then verifies it's within your required 1-30 range. This rejects values like 0t (too low) or 31T (too high).

Quick Valid/Invalid Test Cases: #

• ✅ Valid: 1t, 15T, 30t
• ❌ Invalid: 0T, 31t, x12T, 123 (no t/T suffix)

内容的提问来源于stack exchange，提问作者unnamed