Hey guys, I'm pretty new to SQL Lite and I hope to get help regarding my problem.

I have a table called "CUSTOMERS". I want to display the columns "ID", "name" and "description" in the console. But all empty fields in the column "description" should be replaced with the text "no description".

I don't want to change anything in the table itself, only display it in the console. So I need a SELECT command to accomplish this.

My current code is the following:

SELECT id, name, description, FROM CUSTOMERS WHERE name = 'WOOL' ORDER BY description ASC;

How can I built in this existing code, that all empty fields in the column "description" should be displayed as "no description"?

I'm very thankful for every answer. I hope you can answer with a fitting code as I my knowledge is currently very limited.

Thank you guys in advance.

If by "empty" you mean NULL, then you can use coalesce. If you mean an empty string, then you can use iif or the more general case expression

SELECT id, 
       name, 
       IFNULL(NULLIF(description, ''), '(no description)') AS Description
  FROM CUSTOMERS
 WHERE name = 'WOOL'
 ORDER BY description ASC
;

NULLIF(x, y) means return NULL if x=y, else return x. (x may still be null by itself)

IFNULL(x, y) means return y if x is NULL, else return x.

so it follows that: IFNULL(NULLIF(x, y), b) will return x if x <> y and x is not NULL, else it will return b.

and so: IFNULL(NULLIF(description, ''), '(no description)') will return "description" if it is not null and not empty ('') else it will return '(no description)'.

This is also a useful pattern to use to avoid divide by zero errors: SELECT IFNULL( x / NULLIF(y, 0), 0) as ratio will return 0 if y is NULL or y is 0, in stead of throwing a DIV0 error.

EDIT: Let me add COALESCE for completeness on the subject: COALESCE() is similar to IFNULL() but it allows multiple parameters to successively substitute, where IFNULL() allows only 1 substitute.
COALESCE(x1, x2, ..., xn) will return the first x parameter that is not NULL, or NULL if all parameters are NULL.

That is:
COALESCE(x, y) is equivalent to IFNULL(x, y), and
COALESCE(a, b, c, d) is equivalent to IFNULL(a, IFNULL(b, IFNULL(c, d))) - which also makes it clear why COALESCE() is useful.

Note that the function names are sometimes different in other SQL engines, such as MSSQL which has "ISNULL()" in stead of "IFNULL()", etc.

Thanks to all for prior responses. Empty in my case means NULL, '', or ' ' (any number of spaces) In all cases, I want to substitute such emptiness with something. And the only way I see to do it is to use IFNULL(NULLIF(LTRIM(RTRIM( Which is a lot to type. Is there a better way?

I'd suggest iif( b1, v1, b2, v2, b3, v3, … v) .

You can put as many tests (the 'b' values) in as you like, testing for null, empty string, 'n/a, etc.. In your case all the 'v' values would be 'no description', except for the last one, which has no test and would be whatever the real contents of the field is.

If you are designing the database yourself, or if you can control what data gets added in future, then I should tell you that it's bad form to allow many different 'not known' forms to be stored: they should all be stored as NULL. This is literally what NULL was invented for: it means 'not known'.

On the other hand, if you have to work with a database you can't change, or are not able to fix existing data, then your question is valid.

Thanks Simon. I agree, NULL should be used for unknown. In my case, tables are created from arbitrary csv files, using .import, and first line indicates column names. Empty columns become '', not NULLs in this case. Perhaps the system could be redisigned, but right now, it is this.

Wrt iif(): it probably can't cover empty strings of unknow number of spaces on its own, LTRIM(RTRIM()) still seems to be necessary

empty strings of unknown number of spaces ... LTRIM(RTRIM()) still seems to be necessary

If you're just checking if you've got an empty string after removing spaces, then you only need to trim from one end, not both.

...and for other purposes, if you did need to trim from both ends, TRIM() is less typing and probably slightly more efficient than nesting LTRIM(RTRIM()).

if you are in a position to define the table, the following will do the work for you

sqlite> CREATE TABLE t (id, name, desc, description AS (
(x2...>   Iif(desc IS NULL OR Trim(desc) = '', 'no description', desc)
(x2...> ) VIRTUAL);
sqlite> INSERT INTO t (id, name, desc) VALUES (1, 'foo', 'blah blah'), (2, 'bar', ''), (3, 'baz', '   '), (4, 'qux', null);
sqlite> SELECT id, name, description FROM t;
id  name  description
--  ----  --------------
1   foo   blah blah
2   bar   no description
3   baz   no description
4   qux   no description
sqlite>

edit: added test for null values