COALESCE Function in TSQL

by

I’m not sure why you think the documentation is vague.

It simply goes through all the parameters one by one, and returns the first that is NOT NULL.

COALESCE(NULL, NULL, NULL, 1, 2, 3)
=> 1


COALESCE(1, 2, 3, 4, 5, NULL)
=> 1


COALESCE(NULL, NULL, NULL, 3, 2, NULL)
=> 3


COALESCE(6, 5, 4, 3, 2, NULL)
=> 6


COALESCE(NULL, NULL, NULL, NULL, NULL, NULL)
=> NULL

It accepts pretty much any number of parameters, but they should be the same data-type. (If they’re not the same data-type, they get implicitly cast to an appropriate data-type using data-type order of precedence.)

It’s like ISNULL() but for multiple parameters, rather than just two.

It’s also ANSI-SQL, where-as ISNULL() isn’t.

More Related Contents:

  1. Is it possible to write single query on two table which are not connected to each other?
  2. T-SQL split string
  3. Unpivot with column name
  4. How can I get column names from a table in SQL Server?
  5. Conditional WHERE clause in SQL Server
  6. How can I select the first day of a month in SQL?
  7. Split function equivalent in T-SQL?
  8. Is the NOLOCK (Sql Server hint) bad practice?
  9. Multi-statement Table Valued Function vs Inline Table Valued Function
  10. Convert varchar into datetime in SQL Server
  11. Select statement to find duplicates on certain fields
  12. Is having an ‘OR’ in an INNER JOIN condition a bad idea?
  13. Where value in column containing comma delimited values
  14. Check if a row exists, otherwise insert
  15. T-SQL datetime rounded to nearest minute and nearest hours with using functions
  16. Parse comma-separated string to make IN List of strings in the Where clause
  17. Compare DATETIME and DATE ignoring time portion
  18. Split function in SQL Server 2008
  19. Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)
  20. Cannot use a CONTAINS or FREETEXT predicate on table or indexed view because it is not full-text indexed
  21. TransactSQL to run another TransactSQL script
  22. Can I set ignore_dup_key on for a primary key?
  23. Store multiple bit values in a single table column
  24. Which SQL query is faster? Filter on Join criteria or Where clause?
  25. SQL Server equivalent to MySQL enum data type?
  26. How can I group by date time column without taking time into consideration
  27. Using IF ELSE statement based on Count to execute different Insert statements
  28. SQL Server SELECT INTO @variable?
  29. Using CASE Statement inside IN Clause
  30. SQL Server: +(unary) operator on non-numeric Strings

Leave a Comment