CODEWITHSUNDEEP
sqlsqlite
Satya Prakash Panigrahi
Satya Prakash Panigrahi

Reputation: 789

SQLite syntax for If Else condition

I am using SQLite database. My table has a text column called "Password". Earlier for retrieving the values I used to execute a simple select * from myTable query. But now the requirement is that if Password value is not NULL then I need to show it as "Yes" or otherwise "No". It's something like:

select * from myTable
if Password != NULL then Password = 'Yes'
else Password = 'No'

I searched a lot regarding this but I didn't get any proper link or example. Any help or suggestion would be really appreciated.

Upvotes: 56

Views: 129645

Answers (4)

NearHuscarl
NearHuscarl

Reputation: 81370

You can use IIF() function from SQLite v3.32.0+ to shorten your code even more:

SELECT
  Password,
  IIF(Password IS NOT NULL, 'Yes', 'No') [HasPassword]

Upvotes: 14

Kunal Jhingan
Kunal Jhingan

Reputation: 37

I understand we want to change the value of the password column to "YES" if password has been written in the password column otherwise we change the value to "NO".

We can use the following two queries to make these changes :

UPDATE myTable SET Password = "YES" WHERE Password IS NOT NULL;    
UPDATE myTable SET Password = "NO" WHERE PASSWORD IS NULL;

Upvotes: 2

Nick
Nick

Reputation: 2613

SQLite uses the CASE WHEN THEN END syntax. You can read more about that here: http://www.sqlite.org/lang_expr.html

I have not checked the syntax but I'm guessing something like this:

select * 
from myTable CASE WHEN Password != NULL THEN Password = 'Yes' ELSE Password = 'No' END;

Though I'm not sure that would work at all. If you're trying to get a YES or NO based on the presence of a password in the table for every record, perhaps this is closer to what you want:

SELECT Field1, 
    Field2,
    (CASE WHEN Password != NULL THEN 
        'Yes' 
     ELSE 
        'No' 
     END) as 'Password'
FROM myTable;

Again, I do not currently have access to SQLite to test this myself so it probably needs some work.

Upvotes: 22

CL.
CL.

Reputation: 180060

SELECT *,
       CASE WHEN Password IS NOT NULL
       THEN 'Yes'
       ELSE 'No'
       END AS PasswordPresent
FROM myTable

Upvotes: 99

Related Questions