How to make a query in sql that returns the data of a table when the data is empty? [duplicate]

2

Well, I created a table called installments, which when the customer pays it is entered 'PAYMENT' in the column situation, and when not paid it remains blank. I wanted to get the information from this column to know when the account was not paid, so bring only the customers that have the column empty.

Example:

select * from parcela where situacao =''

But do not bring the data from the table situation and when I put:

select * from parcela where situacao ='PAGO'

It brings the data perfectly

    
asked by anonymous 29.10.2017 / 16:09

1 answer

5

Watch out! There is a lot of confusion when it says that a certain value is "empty."

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

  • null content [ NULL ];
  • blank content [ length(s) == 0 ];
  • Content with only whitespaces [ length(s) != 0 and length(trim(s)) == 0 ].
  • For you to be able to identify a field in any of the above situations, you would need 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: ( as mentioned in the @CleberGriff comment )

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

    Or, specifically in the scenario exposed in your question:

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

    See working in SQLFiddle

        
    29.10.2017 / 21:29