MySQL - Replace NULL or empty with 0000

Question

I try to replace null value with string 0000

SELECT customer_id
FROM customer c 
LEFT JOIN user u ON u.idc = c.id
WHERE deleted = 0

I try with

IFNULL(c.customer_id,'0000')

COALESCE(c.customer_id,'0000')

REPLACE(c.customer_id,'','0000')

but don't work.

Result should be:

(Before)    (After)
null        0000
1234        1234
            0000

What should I do ?

`COALESECE()` should be getting the job done. Can you post some sample data? — Tim Biegeleisen, Feb 22 '16 at 09:31
Possible duplicate of [MYSQL: replace null with 0?](http://stackoverflow.com/questions/3532776/mysql-replace-null-with-0) — Pathik Vejani, Feb 22 '16 at 09:32
@PathikVejani Should be same but in my case not return 0000 it return empty field — May, Feb 22 '16 at 09:35
`SELECT IF(c.customer_id IS NULL OR c.customer_id = '', '0000', c.customer_id)` should work fine. — h2ooooooo, Feb 22 '16 at 09:39
@Shadow Sorry :( I will do better next time, but I think my question might be clear with Replace NULL or empty phrase. — May, Feb 22 '16 at 09:48

score 1 · Accepted Answer · answered Feb 22 '16 at 09:39

1

Use inline IF to check for null and empty strings and if exists then return '0000'.

SELECT IF(customer_id='' OR customer_id IS NULL,'0000',customer_id)
FROM customer c 
LEFT JOIN user u ON u.idc = c.id
WHERE deleted = 0

answered Feb 22 '16 at 09:39

Abhishek Ginani

4,511
4
23
35

It's work and easy for me same as @h2ooooooo 's answer :) – May Feb 22 '16 at 09:50

score 1 · Answer 2 · answered Feb 22 '16 at 09:42

1

SELECT CASE 
WHEN customer_id ='' THEN replace(customer_id,' ','0000')
WHEN customer_id is null THEN replace (customer_id,null,'0000')
end AS customer_id
FROM customer c 
LEFT JOIN user u ON u.idc = c.id
WHERE deleted = 0

answered Feb 22 '16 at 09:42

Ankit Agrawal

2,426
1
13
27

score 0 · Answer 3 · answered Feb 22 '16 at 09:35

0

you can check for empty first like this:

SELECT case when customer_id='' then '0000' else COALESCE(c.customer_id,'0000') end 
FROM customer c 
LEFT JOIN user u ON u.idc = c.id
WHERE deleted = 0

answered Feb 22 '16 at 09:35

Gouda Elalfy

6,888
1
26
38

I didn't downvote you, but I suspect the reason is that the OP is already essentially using your query and still did not get the desired result. – Tim Biegeleisen Feb 22 '16 at 09:39

score 0 · Answer 4 · answered Feb 22 '16 at 09:39

If you have both null values and empty strings, then you need to combine coalesce() with string replacement:

SELECT if(coalesce(customer_id,'0000')='','0000',coalesce(customer_id,'0000')) as customer_id
FROM customer c 
LEFT JOIN user u ON u.idc = c.id
WHERE deleted = 0

MySQL - Replace NULL or empty with 0000

4 Answers4