SQLite syntax for If Else condition

Question

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.

NEVER compare anything with NULL (!=NULL). Instead use IS NULL (IS NOT NULL). Because this condition is ALWAYS FALSE NULL=NULL -> FALSE NULL!=NULL -> FALSE. — valex
– valex, Commented Jan 29, 2013 at 12:39
Thanks Valex. I actially am using (<>'') instead of (IS NOT NULL) — Satya Prakash Panigrahi
– Satya Prakash Panigrahi, Commented Jan 29, 2013 at 12:49
Question is old but for all still reading this, please always use IS NULL / IS NOT NULL as expression, neither ==, != nor <>. — MKesper
– MKesper, Commented Feb 15, 2017 at 7:26
The Coalesce Sql command allows for a default value when a field is null, it is supported by SQLITE: sqlite.org/lang_corefunc.html#coalesce — Dave E
– Dave E, Commented Jan 8, 2024 at 11:44

CL. · Accepted Answer · 2013-01-29 10:45:01Z

101

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

answered Jan 29, 2013 at 10:45

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Anup · Accepted Answer · 2017-03-03 09:34:27Z

21

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.

edited Mar 3, 2017 at 9:34

Anup

4881 gold badge19 silver badges27 bronze badges

answered Jan 29, 2013 at 10:09

Nick

2,61121 silver badges21 bronze badges

2 Comments

Satya Prakash Panigrahi Over a year ago

Thanks Nick. I will try working on your suggestion. It's correct, I need to check the presence of Password in every record. But even in your 2nd suggestion use of "select * ..." would be more convenient. Anyway I will try implementing them and reply you with the feedback.

Broxzier Over a year ago

Note that Password != NULL will always be true, since comparing something with NULL is not possible, like how you can't compare anything to nothing. You should use WHEN Password IS NOT NULL instead.

NearHuscarl · Accepted Answer · 2021-11-24 16:56:37Z

16

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]

answered Nov 24, 2021 at 16:56

NearHuscarl

83.1k24 gold badges321 silver badges291 bronze badges

Comments

David Buck · Accepted Answer · 2020-08-29 06:21:40Z

2

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;

edited Aug 29, 2020 at 6:21

David Buck

3,87740 gold badges54 silver badges73 bronze badges

answered Aug 29, 2020 at 2:50

Kunal Jhingan

371 bronze badge

1 Comment

elig Over a year ago

poster did not ask to modify the table

Collectives™ on Stack Overflow

SQLite syntax for If Else condition

4 Answers 4

Comments

2 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related