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

Question

Consider the following sqlite3 table:

+------+------+
| col1 | col2 |
+------+------+
| 1    | 200  |
| 1    | 200  |
| 1    | 100  |
| 1    | 200  |
| 2    | 400  |
| 2    | 200  |
| 2    | 100  |
| 3    | 200  |
| 3    | 200  |
| 3    | 100  |
+------+------+

I'm trying to write a query that will select the entire table and return 1 if the value in col2 is 200, and 0 otherwise. For example:

+------+--------------------+
| col1 | SOMEFUNCTION(col2) |
+------+--------------------+
| 1    | 1                  |
| 1    | 1                  |
| 1    | 0                  |
| 1    | 1                  |
| 2    | 0                  |
| 2    | 1                  |
| 2    | 0                  |
| 3    | 1                  |
| 3    | 1                  |
| 3    | 0                  |
+------+--------------------+

What is SOMEFUNCTION()?

Thanks in advance...

You could use something like what is described in [this answer.](http://stackoverflow.com/questions/63447/how-do-you-perform-an-if-then-in-an-sql-select) — Adam, Jun 10 '14 at 18:58

score 2 · Accepted Answer · answered Jun 10 '14 at 19:24

2

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

answered Jun 10 '14 at 19:24

CL.

173,858
17
217
259

Thanks. Your answer is the simplest. – Barry Fruitman Jun 10 '14 at 20:48

score 1 · Answer 2 · edited May 23 '17 at 12:06

1

Like described in Does sqlite support any kind of IF(condition) statement in a select you can use the case keyword.

SELECT col1,CASE WHEN col2=200 THEN 1 ELSE 0 END AS col2 FROM table1

edited May 23 '17 at 12:06

Community

1
1

answered Jun 10 '14 at 19:23

Paul Kertscher

9,416
5
32
57

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

2 Answers2