473,231 Members | 1,383 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 473,231 developers and data experts.

How to use COALESCE SQL function

13,262 8TB
This is a short tip describing the sql coalesce function.

Description

COALESCE accepts a comma separated list of parameters and returns
the first value that is not null in that list or null if all the arguments are null.
Thus
Expand|Select|Wrap|Line Numbers
  1. COALESCE (a1, a2, a3, ..., an)
will return a1 if a1 is not null, a2 if a1 is null but a2 is not null,
a3 if a1, a2 are null but a3 is not null and so on.

The same thing can be achieved by using a CASE. For the snippet above the CASE would be

Expand|Select|Wrap|Line Numbers
  1. CASE WHEN (a1 IS NOT NULL) THEN a1
  2. WHEN (a2 IS NOT NULL) THEN a2
  3. WHEN (a3 IS NOT NULL) THEN a3
  4. ...
  5.  
  6. WHEN (an IS NOT NULL) THEN an
  7. ELSE NULL
Notice that the ELSE NULL at the end ensures that NULL is returned
when all the arguments are null to conform to the coalesce behaviour.
Clearly the coalesce version is much more compact.

Common uses

Preferential selection



  • Usually used when many columns can be used to represent a value but only one should be used at a time.
In this case the parameters are normally specified starting with the most specific or detailed representation.
Expand|Select|Wrap|Line Numbers
  1. SELECT  COALESCE (name, description) as Product FROM Products 
will show the description only if the name is null.
Optional parameters



  • Coalesce can also be used to provide a way of passing optional parameters.
Normally for a parameter say $P you would do
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM Products WHERE (name = '$P' OR $P IS NULL)
If the parameter $P is passed as NULL all the products will be selected otherwise only
the product with name $P is selected. With coalesce, this can be done as:
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM Products WHERE (name  LIKE COALESCE($P, '%' ))
Portability

This function is an ANSI standard function and so is available in most
RDBMs and generally more advisable to use than RDMS specific
functions (e.g SQL Server's ISNULL) .

Hope this was useful.
Jul 24 '08 #1
1 28767
JamieHowarth0
533 Expert 512MB
Another good use of COALESCE is in UPDATE statements. To prevent having to resubmit an entire record, you can add a listener for which fields have changed from the underlying data source, and then your update procedure would be similar to the following:
Expand|Select|Wrap|Line Numbers
  1. CREATE PROCEDURE updatemyrecord
  2.     @myparam1 nvarchar(255),
  3.     @myparam2 nvarchar(255),
  4.     @myparam3 nvarchar(255),
  5.     @myparam4 nvarchar(255),
  6.     @recordid int
  7. AS
  8. BEGIN
  9.     UPDATE mytable SET
  10.     MyColumn1 = COALESCE(@myparam1, MyColumn1),
  11.     MyColumn2 = COALESCE(@myparam2, MyColumn2),
  12.     MyColumn3 = COALESCE(@myparam3, MyColumn3),
  13.     MyColumn4 = COALESCE(@myparam4, MyColumn4)
  14. WHERE RecordId = @recordid
  15. END
  16.  
This prevents the need for submitting every value to the database, just the ones that have changed, and thus helping to prevent accidental loss of data.

medicineworker
Oct 3 '08 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: AzGhanv/. | last post by:
Hi, I m searching for some Script / Function ... to find difference in data b/w 2 similar tables (exactly same fields structure) in sql 2000. plz update me asap ! thanks in advance !
24
by: Edmund Dengler | last post by:
Hi all! I am doing some trigger functions that need to find a tuple in another table. The problem is that this second table is doing some summarization work, and I need nulls to equal each...
5
by: John Greve | last post by:
Any ideas on why my the result 'green' row column DELTA does not yield -1 for? That is not how I expected COALESCE( ... ) to work. Every other row for DELTA (including 'black') comes out the...
1
by: Bruno BAGUETTE | last post by:
Hello, I'm trying to build a PL/PGSQL function that will be called by a trigger which will update a table named 'mview_contacts'. That table plays the role of a materialized view. The...
5
by: Simon Windsor | last post by:
Hi Is there a standard postgres method of replacing empty strings. In Oracle, nvl handles nulls and empty strings, as does ifnull() in MySQL, but with postgres coalesce only handles null...
4
by: Don | last post by:
If I have an SQL query which returns an aggregate of several decimal fields like so: (sum(COALESCE(myDecimal1, 0)+ sum(COALESCE(myDecimal2, 0)+ sum(COALESCE(myDecimal3, 0)) as MyTotal I get...
10
by: db2group88 | last post by:
hi, we are using db2 v8.2 EE on windows, with jcc driver. try to create a join view, after that i check the syscat.columns table, for those decimal field use COALESCE method, all the length become...
0
by: davidsavill | last post by:
Hi All, I am migrating a database from Firebird/Interbase to DB2 and have having issues with the stored procedures/functions. I have a number of functions that loop over a FOR loop, each pass...
1
by: =?Utf-8?B?R1ROMTcwNzc3?= | last post by:
Hi All, is this the correct way to Coalesce a session var - <%Session("EMPLOYETOKENS") = tokens.Fields.Item(COALESCE(SUM("JBCLTokens"),0)).Value%? Thank you
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.