How to check if SQLite row is empty

Question

I have the following which only inserts to column two if the value is empty:

UPDATE name 
SET one=?,
    two=COALESCE(two, ?),
    three=? 
WHERE id = ?

However, this only works if column two if the value is null. How can I get this to work if it is either NULL OR an empty string.

It should always insert to one and three. It should only insert to two if it is NULL or empty

I am using SQLite with PHP

Gordon Linoff · Accepted Answer · 2021-05-28 13:19:35Z

1

If by "empty" you mean an empty string, you can use nullif():

two = COALESCE(NULLIF(two, ''), ?)

The more general solution is a CASE expression.

answered May 28, 2021 at 13:19

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

forpas · Accepted Answer · 2021-05-28 13:43:07Z

0

Don't store empty strings in TEXT columns.
Update the table to replace all empty strings with NULLs:

UPDATE name
SET two = NULL
WHERE TRIM(two) = ''

Now you can use your query, which is correct and in any other query you only need to check for NULL.

answered May 28, 2021 at 13:43

forpas

165k10 gold badges51 silver badges85 bronze badges

1 Comment

forpas Over a year ago

@jefiwo the notion of NULL is that it represents missing information (en.wikipedia.org/wiki/Null_(SQL)), while an empty string is a valid string value. Also, just to be practical. Do you want to check 2 (or more) conditions: if the column is NULL, or if it is empty, or if it consists of spaces?

Collectives™ on Stack Overflow

How to check if SQLite row is empty

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related