How do I display empty fields in a column with "no description"?

(1) By RosDon on 2022-07-20 11:59:47 [link] [source]

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.

(2) By David Raymond (dvdraymond) on 2022-07-20 12:20:13 in reply to 1 [link] [source]

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

(3.2) By Ryan Smith (cuz) on 2022-07-20 14:05:32 edited from 3.1 in reply to 1 [link] [source]

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.

(4) By Simon Slavin (slavin) on 2022-07-20 16:48:10 in reply to 1 [source]

There's a question first: what do you mean by 'empty' ?

In SQLite there are two common possibilities for a blank value. One is that you're talking about a zero-length text string. The other is that there is literally no value: the column has a value of NULL. (There are other less-likely possibilities too, for example a zero-length BLOB, or a Unicode sequence which resolves to no text.)

So your original query

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

Could become

SELECT id, name,
        (CASE
            WHEN description='' THEN 'zero-length description'
            WHEN IFNULL(description) THEN 'description is NULL'
            ELSE description
        END) AS desc_text
     FROM CUSTOMERS
     WHERE name = 'WOOL'
     ORDER BY description ASC

for testing purposes. Once you see the result, you can correct the text for that value, and leave out the other test.