Convert string to int inside WHERE clause of SQLITE statment

Question

I want to achieve this:

SELECT * FROM linkledger WHERE toInt(reputation) > 100;

but the function toInt doesnt exist? Is there one?

I found this now but not working, which implies i have a more fundemental problem. BECAUSE THIS IS CORRECT

SELECT * FROM linkledger WHERE cast(reputation AS INTEGER) > 100;

You have a problem because something is correct? What do you mean? — , Oct 23 '14 at 07:11
Well, what is your schema? What is `linkledger`? What is `reputation`? — Lightness Races in Orbit, Oct 23 '14 at 12:00

Anik Islam Abhi · Accepted Answer · 2015-12-22T03:51:32.543

50

Use CAST(reputation AS INTEGER).

edited Dec 22 '15 at 03:51

answered Oct 23 '14 at 07:01

Anik Islam Abhi

25,137
8
58
80

cOrrect answer, I clearly have a more fundemental problem however. – Banned_User Oct 23 '14 at 07:06
What other problem do you have? – Oct 23 '14 at 07:07
thanks @Tichodroma for editing my post. i am new here . it will help me in giving answer next time if you tell me is there any problem in giving link ? or any other thing that should not attach in the answer. thanks in advance – Anik Islam Abhi Oct 23 '14 at 07:09
1

No, this kind of link is fine. You could add an example of how to use `CAST`. – Oct 23 '14 at 07:10
The question says this as the keyword needn't be in uppercase, no? – Lightness Races in Orbit Oct 23 '14 at 12:00

score 2 · Answer 2 · answered Oct 23 '14 at 11:45

2

I will leave Anik Islam Abhi's answer as the correct one because it will apply in most cases but I just want it on record that I only managed to fix the problem by just switching the variable to int

create table foo reputation int;

And then insuring Ints were inserted... I'm not sure what was happening that the correct answer didnt solve my problem.

answered Oct 23 '14 at 11:45

Banned_User

973
2
9
17

What was its type previously? This is almost certainly the issue but you never told us. – Lightness Races in Orbit Oct 23 '14 at 12:01
Yeah I said it in the question title. It was a String. I tried having the engine cast for me, I tried to correct answer which i marked, and i even tried rounding the string. And also tried compairing the string number to a string number. – Banned_User Oct 24 '14 at 00:48
From [the documentation](http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html#function_cast): _"To cast a string to a numeric value in numeric context, you normally do not have to do anything other than to use the string value as though it were a number"_. – Lightness Races in Orbit Oct 24 '14 at 03:37

Convert string to int inside WHERE clause of SQLITE statment

2 Answers2

Linked