Does sqlite support any kind of IF(condition) statement in a select

Question

Does sqlite support the sql function "if" in the select statement?

for example

select if( length( a ) > 4 , a , ' ') as b
from foo

which would return a if the length was over 4 chars long. or else it would return ' ' as b

If it does support a condition in the select what is the syntax is should be using?

mmmmmm · Accepted Answer · 2017-10-05T22:52:38.573

130

See the case expression.

A CASE expression serves a role similar to IF-THEN-ELSE in other programming languages.

For your example

select case when length(a) > 4 then a else '' end as b
from foo

edited Oct 05 '17 at 22:52

answered Aug 18 '09 at 15:44

mmmmmm

1

dunno why they can't also have a shorter syntax for simple things like the ternary operator in C.... imagine how long the query can be when stringing together several case statements ! – Michael Jul 08 '19 at 00:45

score 18 · Answer 2 · edited Jan 31 '12 at 22:53

18

You can use case for that:

select case when length(a)>4 then a else ' ' end from foo;

edited Jan 31 '12 at 22:53

mmmmmm

answered Aug 18 '09 at 15:47

Paul Dixon

Odd; the link doesn't work for iceweasel. It will cause a Document Not Found Error (/lang_5Fexpr.html). The address in the address-bar will be the correct one though (.../lang_expr.html). Not sure why that is. Although the address is correct, a refresh won't work; have to hit enter in the address bar to get the page. – Inshallah Aug 18 '09 at 17:26
markdown doesn't like underscores I guess. I have changed the link to use the snurl redirector – Paul Dixon Aug 18 '09 at 17:48
and you should not use a redirector [ so direct URL is now there – mmmmmm Jan 31 '12 at 22:53

score 5 · Answer 3 · answered Aug 10 '20 at 09:57

Since version 3.32.0 you can use iif(X,Y,Z):

select iif( length( a ) > 4 , a , ' ') as b
from foo

From the codumentation:

The iif(X,Y,Z) function is logically equivalent to and generates the same bytecode as the CASE expression "CASE WHEN X THEN Y ELSE Z END".

3 Answers3