Barry Fruitman
Barry Fruitman

Reputation: 12656

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

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...

Upvotes: 1

Views: 639

Answers (2)

CL.
CL.

Reputation: 180080

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

Upvotes: 2

Paul Kertscher
Paul Kertscher

Reputation: 9723

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

Upvotes: 1

Related Questions