Compare Strings as Numbers in SQLite3

SELECT * FROM t1 ORDER BY t1.field + 0

grp	value
1	'10 100 20 5 70'
2	'100 20 5 35 3 15'

An unexpected result of comparing strings in Sqlite

It looks like you are making it a string which you can't compare an int to a string. In Python I ran:

SELECT * FROM tab WHERE rowid > 9

And it worked fine try changing it from a literal (the " ' ") to an int.

To set your varchar(s) to int you can cast it so you don't change your table at all, using:

CAST(uid AS INT)

you can also (as @laalto mentioned) compare two strings with the >/< sign, so for that just I believe you can cast the number as a string (but I do believe it most be done with the CAST statement, not just the quotes) Note I could be wrong about this though as I have never used either I just cast the ID as an INT.

If you are interested in the CAST command, you can read more here.
and of course the official documentation here

Here's a copy and paste version for you as well:

SELECT * FROM 'IMMessageInfo' WHERE CAST(uid as INT) > 10000 ORDER BY uid DESC

(full code is as follows (It is python 2.7.6; so it may look a little different from what you are using :) )

import sqlite3

conn = sqlite3.connect('database.db')
c = conn.cursor()

c.execute('''CREATE TABLE IF NOT EXISTS tab (rowid int, data text)''')

c.execute("INSERT INTO tab (rowid, data) VALUES (10, 'MynameisMike')")
conn.commit()

c.execute("SELECT * FROM tab WHERE rowid > 9")
print(c.fetchone())

I'm confused about Sqlite comparisons on a text column

It is all explained in the Datatypes In SQLite Version 3 article. For example, the answer to the first portion of questions is

An INTEGER or REAL value is less than any TEXT or BLOB value. When an INTEGER or REAL is compared to another INTEGER or REAL, a numerical comparison is performed.

This is why SELECT 9 < '1' and SELECT 9 < '11' both give 1 (true).

The expression "a BETWEEN b AND c" is treated as two separate binary comparisons "a >= b AND a <= c"

The most important point to know is that column type is merely an annotation; SQLite is dynamically typed so each value can have any type.

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"

---

Related Topics