SQL Query “like” operator error, when the field is null

this query returns 421 records:

Now when I implement the “like” operator, it returns 349 records, it is not taking into account the records with the field “invoice_number” in null:

Answer

A condition like null like '%%' is false. null is not like anything. So if you want to allow null values, you need to be explicit about it:

and (ds.numero_factura like '%%' or ds.numero_factura is null)

Or you can use coalesce():

 and coalesce(ds.numero_factura, '') like '%%'

This condition does not really make sense though (basically it will allow all records), so you would need to adapt it to your exact use case.

Another remark is that ds.numero_factura looks like a number; in that case, you don’t want to use string functions on it: use number functions instead.