coalesce in SQL
Coalesce is used to check a coumn and if it is null or empty then fill with another alternative value.
Check Null and empty("") value in SQL
This will check a particular column value and if it is null or empty then it will replace with another value.
select Coalesce(NullIf('Rajesh', ''), '--') --MiddleName=>return Rajesh select Coalesce(NullIf('', ''), '--') MiddleName => return --
Usage of coalesce
In below table Ph_no, Alt_no and Office_no these field having null field.
id
|
Name
|
Ph_ no
|
Alt_ no
|
Office_no
|
101
|
Albert
|
999999
|
456453
|
321333
|
102
|
khan
|
null
|
null
|
123455
|
103
|
victor
|
112121
|
null
|
null
|
104
|
lovely
|
null
|
null
|
1897321
|
If you want to display a column and if it is null or empty then we can set alternative column fields, in below example we filled three alternative column.
1. First check the Ph_no if it is null or empty then fill Alt_no
2. If Alt_no also empty or null then fill Office_no
select id , name ,coalesce(Ph_no,Alt_no,Office_no) as contactnumber from employee
id
|
Name
|
Contactnumber
|
101
|
Albert
|
999999
|
102
|
khan
|
123455
|
103
|
victor
|
112121
|
104
|
lovely
|
1897321
|
0 comments:
Post a Comment