Checking empty column with CASE in Mysql

Asked

Viewed 1,593 times

4

I’m having a hard time making a query in a customer table. I need to find two columns (nomeCli and nomeFantasiaCli) and turn them into one: Nome.

In short, when the client’s name is not filled in the client table, I will use the client’s company’s fancy name.

I’m using the following query:

SELECT (CASE nomeCli
          WHEN NULL THEN nomeFantasiaCli
          ELSE nomeCli
        END ) AS Nome
  FROM cliente
 ORDER BY Nome ASC

The query works. The problem is that it only fills the new column Nome only with the nomeCli. If that nomeCli is empty, should fill with nomeFantasiaCli, but that does not happen. I have tried to change the WHEN NULL for WHEN ''. But it didn’t help.

How to solve the problem?

mysql

2 answers

6


You can use the COALESCE function for this.

  SELECT COALESCE(NULLIF(nomeCli,''), nomeFantasiaCli)
  FROM cliente

1

I did some tests, but use SQL SERVER... try to do so:

SELECT (CASE 
      WHEN nomeCli IS NULL THEN nomeFantasiaCli
      ELSE nomeCli
    END ) AS Nome
FROM cliente
ORDER BY Nome ASC

Browser other questions tagged mysql

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