How to Check If a Value Is a Number in SQLite

How to check if a value is a number in SQLite

From the documentation,

The typeof(X) function returns a string that indicates the datatype of the expression X: "null", "integer", "real", "text", or "blob".

You can use where typeof(mycolumn) = "integer"

Check if contents of TEXT column are purely numeric

You have to check whether all values can be converted into an integer:

SELECT *
FROM MyTable
WHERE CAST(MyColumn AS INTEGER) IS NOT MyColumn;

SQL CHECK() how to check if input is numeric value?

You can use GLOB operator:

CREATE TABLE Code ( 
  PIN TEXT NOT NULL,
  title TEXT,
  CHECK(title GLOB '[0-9][0-9][0-9][0-9][0-9]')
);

See the demo.

How can I check for specific values of varchar in sqlite

My first thought was to combine CAST and SUBSTR to check the value of all but the last character in the string:

CREATE TABLE tablename( ex1 VARCHAR NOT NULL 
    CHECK ( ex1 LIKE '%t') 
    CHECK ( CAST(SUBSTR(ex1, 1, LENGTH(ex1)-1) AS INT) BETWEEN 1 AND 30 )
);

Unfortunately, due to the very permissive way that CAST casts strings to integers (simply ignoring anything after the first non-digit), that will also match a lot of undesirables like '4.5t' or even '23qwerty45t'.

A messier but more accurate approach, since we're talking about only one or two digits, would be to separately check the one-digit and two-digit cases:

CREATE TABLE tablename( ex1 VARCHAR NOT NULL 
    CHECK ( ex1 LIKE '%t') 
    CHECK ( 
            (
              (LENGTH(ex1) = 2) 
                AND 
              (CAST(SUBSTR(ex1, 1, 1) AS INT) BETWEEN 1 AND 9)
            )
              OR
            (
              (LENGTH(ex1) = 3) 
                AND 
              (CAST(SUBSTR(ex1, 1, 2) AS INT) BETWEEN 10 AND 30)
            )
          )
);

 

sqlite> INSERT INTO tablename VALUES('1t');
sqlite> INSERT INTO tablename VALUES('30t');
sqlite> INSERT INTO tablename VALUES('31t');
Error: CHECK constraint failed: tablename
sqlite> INSERT INTO tablename VALUES('0t');
Error: CHECK constraint failed: tablename
sqlite> INSERT INTO tablename VALUES('111t');
Error: CHECK constraint failed: tablename
sqlite> INSERT INTO tablename VALUES('tt');
Error: CHECK constraint failed: tablename
sqlite> INSERT INTO tablename VALUES('01t');
Error: CHECK constraint failed: tablename
sqlite> INSERT INTO tablename VALUES('12.5t');
Error: CHECK constraint failed: tablename
sqlite> INSERT INTO tablename VALUES('23qwerty45t');
Error: CHECK constraint failed: tablename

How to check if a string contains a value in the database?

You can use sqlite instr:

instr(X,Y)

The instr(X,Y) function finds the first occurrence of string Y within string X and returns the number of prior characters plus 1, or
0 if Y is nowhere found within X. Or, if X and Y are both BLOBs, then
instr(X,Y) returns one more than the number bytes prior to the first
occurrence of Y, or 0 if Y does not occur anywhere within X. If both
arguments X and Y to instr(X,Y) are non-NULL and are not BLOBs then
both are interpreted as strings. If either X or Y are NULL in
instr(X,Y) then the result is NULL.

Something like:

SELECT * from table
WHERE instr("Let's see the value of John Smith in the database",name) <> 0

You might want to lower() or upper() all strings to handle case.

Is there a sqlite function that checks if cell value exists

Normall method is to use SELECT to check if there are already values in database. If you get zero results then you can add new value.

Something like this (it may need also cursor, fetch() or fetchall(), etc.)

results = db.execute("SELECT ...") 

if results: 
    #... code ...

#or 

if len(results) > 0:
    #... code ...

---

Probably you may have to do it in with two SELECT queries

results = db.execute("SELECT ... FROM ... WHERE PName = ... ") 

if results: 
    print("ERROR: PName exists with PCode", results[0])
else:
    results = db.execute("SELECT ... FROM ... WHERE PCode = ... ") 
    if results: 
         print("PCode exists with PName", results[1])
         db.execute("UPDATE ...") 
    else:
         db.execute("INSERT ...") 

---

EDIT:

Minimal working code

import sqlite3

# --- functions ---

def drop_db(db):
    db.execute('''DROP TABLE IF EXISTS product''')
    db.commit()

def create_db(db):
    db.execute('''CREATE TABLE IF NOT EXISTS product (
        id INTEGER PRIMARY KEY,
        PCode INTEGERT,
        PName TEXT
    );''')
    db.commit()

def insert_example_data(db):    
    db.execute("INSERT INTO product (PCode, PName) VALUES (1, 'Phone')")
    db.execute("INSERT INTO product (PCode, PName) VALUES (2, 'Car')")
    db.commit()    

def test(db, code, name):
    print('--- test:', name, code, '---')

    result = db.execute("SELECT * FROM product WHERE PName=?", (name,)).fetchone()

    if result:
        print('ERROR PName exists with PCode:', result[1], 'and ID:', result[0])
    else:
        result = db.execute("SELECT * FROM product WHERE PCode=?", (code,)).fetchone()
        if result:
            print('PCode exists with PName:', result[2], 'and ID:', result[0])
            print('UPDATE PName to:', name)
            db.execute('UPDATE product SET PName=? WHERE Pcode=?', (name, code))
            db.commit()
        else:
            print('INSERT')
            db.execute('INSERT INTO product (PCode, PName) VALUES (?, ?)', (code, name))
            db.commit()

def display(db, code=None, name=None):
    print('--- display', code, name, '---')
    if code is None and name is None:
        print('** need code and/or name **')
        return

    if code is not None and name is None:
        results = db.execute("SELECT * FROM product WHERE PCode=?", (code,))
    if code is None and name is not None:
        results = db.execute("SELECT * FROM product WHERE PName=?", (name,))
    if code is not None and name is not None:
        results = db.execute("SELECT * FROM product WHERE PCode=? AND PName=?", (code, name,))

    for row in results:
        print(row)

# --- main ---

db = sqlite3.Connection("data.sqlite")

drop_db(db)
create_db(db)
insert_example_data(db)

test(db, 3, 'Car')
test(db, 7, 'Boat')
test(db, 7, 'House')

display(db, code=7)
display(db, name='Boat')
display(db, name='House')
display(db)

db.close()

Is there an sqlite function that can check if a field matches a certain value and return 0 or 1?

In SQLite, boolean values are just integer values 0 and 1, so you can use the comparison directly:

SELECT col1, col2 = 200 AS SomeFunction FROM MyTable

Check digit constraint in SQLite

Since you defined license_id as INTEGER PRIMARY KEY there is no need to check if the value that you insert is an integer by checking each of its characters with ... BETWEEN 0 AND 9.

In fact this is the only case that SQLite forces type checking, so you can be sure that non integer values will be rejected.

Also in your code you are mixing boolean expressions and substring() which do not make sense.

Focus only in the check constraint rules and it can be done like this:

CREATE TABLE GameLicense_16 (
  title TEXT,
  release_year INTEGER,
  platform TEXT,
  license_id INTEGER PRIMARY KEY
  CHECK (LENGTH(license_id) == 5)
  CHECK (
    SUBSTR(license_id, 5, 1) + 0 = ( 
      SUBSTR(license_id, 1, 1) + 3 * SUBSTR(license_id, 2, 1) +
      SUBSTR(license_id, 3, 1) + 3 * SUBSTR(license_id, 4, 1)
    ) % 10
  )  
);

See the demo.

How to tell if a value exists in a sqlite3 database, python

All you have to do is make a query before insertion, and do a fetchone. If fetchone returns something, then you know for sure that there is a record already in the DB that has the email OR username:

def signup():
    email = request.form['email']
    username = request.form['user']
    password = request.form['password']

    # Create cursor object
    cur = g.db.cursor()

    # run a select query against the table to see if any record exists
    # that has the email or username
    cur.execute("""SELECT email
                          ,username
                   FROM users
                   WHERE email=?
                       OR username=?""",
                (email, username))

    # Fetch one result from the query because it
    # doesn't matter how many records are returned.
    # If it returns just one result, then you know
    # that a record already exists in the table.
    # If no results are pulled from the query, then
    # fetchone will return None.
    result = cur.fetchone()

    if result:
        # Record already exists
        # Do something that tells the user that email/user handle already exists
    else:
        cur.execute("INSERT INTO users VALUES (?, ?, ?)", (email, username, password))
        g.db.commit()

---

Related Topics