How to correctly search for fields of type NULL through LIKE?

Question

Transact SQL

How to build a query correctly? There is a field col1 in the table nametable, its data type is varchar(4), the default value is null, because it is not always filled.
It is necessary when entering text in text_pole_1 and pressing the key to display the records corresponding to the query. The problem is that since the default field is null, it is possible to display empty records with the query: But then there will only be empty records. If use:
SELECT * FROM nametable WHERE col1 is null;

SELECT * FROM nametable  WHERE col1 like '%"+ui->text_pole_1->text()+"%' or col1 is null;

Both empty and those that match the query in the text field will be displayed.
But in this case, empty fields are not displayed at all, even with an empty text field

SELECT * FROM nametable  WHERE col1 like '%"+ui->text_pole_1->text()+"%';

Answer 1

T-SQL has IsNull()
MySQL has IfNull()
There is also COALESCE() and NVL()
In these examples, we have replaced NULL with an empty string.

Answer 2

You start with the question - what do you want to get? Based on this, already construct a request. Because all 3 queries in the question are completely correct and exactly what should behave differently.
With options like function(field) like 'pattern' - more accurate. For most subds, this means a conscious refusal to use indexes on this field. For like '%pattern%', however, this is true for any btree, a non-prefix search to conduct a btree is naturally not an effective thing.
PS: and oh, I have a big concern about this fragment that you have sql injections open to their full width.

Answer 3

null cannot be compared to null
To get around, you need to replace null with something else, such as a space, and then you can compare.
Example for oracle:
SELECT * FROM nametable WHERE nvl(col1,' ') like '%"+ui->text_pole_1->text()+"%';
the empty search query itself must also be replaced with a space.