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 ?
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
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
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
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
COALESECE()should be getting the job done. Can you post some sample data?