CODEWITHSUNDEEP
sqlsqlite
jerrylei
jerrylei

Reputation: 340

An unexpected result of comparing strings in Sqlite

I want to retrieve result set which conditions the value of 'uid' is bigger than '10000', and they are all vchar() type;

Result's like this :

enter image description here

Upvotes: 0

Views: 548

Answers (2)

Steve Byrne
Steve Byrne

Reputation: 1360

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())

Upvotes: 2

cha
cha

Reputation: 10411

You need to cast it as INT, like this:

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

Upvotes: 1

Related Questions