AllInWorld99 provides a reference manual covering many aspects of web programming, including technologies such as HTML, XHTML, CSS, XML, JavaScript, PHP, ASP, SQL,FLASH, jQuery, java, for loop, switch case, if, if else, for...of, for...in, for...each,while loop, blogger tips, blogger meta tag generator, blogger tricks, blogger pagination, client side script, html code editor, javascript editor with instant output, css editor, online html editor, materialize css tutorial, materialize css dropdown list,break, continue statement, label,array, json, get day and month dropdown list using c# code, CSS button,protect cd or pendrive from virus, cordova, android example, html and css to make android app, html code play,telerik show hide column, Transparent image convertor, copy to clipboard using javascript without using any swf file, simple animation using css, SQL etc. AllInWorld99 presents thousands of code examples (accompanied with source code) which can be copied/downloaded independantly. By using the online editor provided,readers can edit the examples and execute the code experimentally.


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.

Coalesce in sql
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

Total Pageviews