How to do a query in sql that returns data from a table when the data is empty?

Asked

Viewed 411 times

2

Well, I created a table called installments, that when the customer pays it is inserted 'PAID' in the column situation, and when not paid it remains blank. I wanted to fetch the information from that column to know when the account has not been paid, so bring only customers who have empty situation column.

Example: 

select * from parcela where situacao =''

But it does not bring the data of the situation table and when I put:

select * from parcela where situacao ='PAGO'

It brings the data perfectly

mysql sql postgresql
  • 2

    solved this: https://answall.com/questions/99229/howtoverifica-se-um-campo-do-tipo-text-ou-ntext-%C3%A9-null-or-empty

  • 1

    You can answer your own question, so it is not being visited with the issue already solved.

1 answer

5


Beware! There is great confusion when you say that a certain value is "empty".

The term "empty" may be referring to different things:

  1. null content [NULL];
  2. blank content [length(s) == 0];
  3. content with white spaces only [length(s) != 0 and length(trim(s)) == 0].

For you to be able to identify a field in any of the above mentioned situations, it would be necessary to construct the following filter: 

SELECT * FROM parcela WHERE (length(trim(situacao)) == 0) OR (situacao IS NULL);

Or:

SELECT * FROM parcela WHERE (trim(situacao) == '') OR (situacao IS NULL);

Or else: (as mentioned in the @Clebergriff comment)

SELECT * FROM parcela WHERE length(trim(ifnull(situacao, ''))) = 0;

Or, specifically in the scenario set out in your question:

SELECT * FROM parcela WHERE (situacao != 'PAGO') OR (situacao IS NULL);

See working on Sqlfiddle

  • 1

    It is also worth using IFNULL to set a default when the value is null: SELECT * FROM parcela WHERE (TRIM(IFNULL(situacao, '')) = '')

Browser other questions tagged mysql sql postgresql

You are not signed in. Login or sign up in order to post.