Checking empty column with CASE in MySQL

Navigation
4

I'm having a hard time performing a query on a customer table. I need to fetch two columns ( nomeCli and nomeFantasiaCli ) and transform them into one: Nome .

In short, when the client name is not populated in the client table, I will use the client's business 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 populates the new column Nome with only nomeCli . If this nomeCli is empty, it should populate with nomeFantasiaCli , but this does not occur. I've even tried to change WHEN NULL to WHEN '' . But it did not help.

How to solve the problem?

    
asked by anonymous 31.01.2014 / 13:01

2 answers

6

You can use the COALESCE function to do this. 

  SELECT COALESCE(NULLIF(nomeCli,''), nomeFantasiaCli)
  FROM cliente
    
31.01.2014 / 13:21
1

I did some testing, but I 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
    
31.01.2014 / 13:48
calculate a probability function in R Using preg_replace invalid password with SHA1