SQLite Forum

HEX function returns empty string on null input, normal?
Login
I believe the essence here is that hex() is a string function and should/will work always returning a string.

This may be useful if for instance the column you insert it into has NOT NULL restriction.

That said, it's very easy to deal with a NULL value should one really need to absolutely have a string. It's probably more useful to leave that choice to the user of the function, since as it stands now, hex() cannot ever distinguish.

I think this warrants a reconsideration from the devs - unless there is a very good reason I'm missing.

Anyway, to the OP, if they don't change it (perhaps backward compatibility scares them), you can get around it rather convolutedly by something like:

```
  SELECT ..., CASE WHEN x IS NULL THEN NULL ELSE hex(x) END, ...

  or if you have the new fancy iif() available ion your version:

  SELECT ..., iif(x IS NULL,NULL,hex(x)), ... etc.

  Note that this next expression won't work because of CASE rules:
  SELECT ..., CASE x WHEN NULL THEN NULL ELSE hex(x) END, ...

```