This is a collection of previously published articles on this site about SQL Server Conditional statements. This is for quick reference.
Conditional Function IIF – SQL Server
Depending on whether the Boolean expression in SQL Server evaluates to true or false, the conditional function IIF returns one of two values. For ease of comprehension, the syntax and examples are provided below. Syntax: IIF (boolean_expression, true_value, false_value) Examples: SELECT IIF( 1=2, ‘TRUE’, ‘FALSE’); Result: FALSE SELECT IIF( 1=2, ‘RIGHT’, ‘WRONG’) Result: WRONG Let’s…
ISNULL in MySQL and SQL Server
ISNULL function behaves differently in MySQL and Microsoft SQL Server. In MySQL, ISNULL tests whether the given expression is NULL. It returns 1 if the expression is NULL otherwise it returns 0. However, in SQL Server, the null values are replaced with user-defined values during the expression evaluation process. Look at the below examples: MySQL:…
CHOOSE Function in SQL Server
In SQL Server, the CHOOSE function returns the item at the provided index from a list of values. CHOOSE is an index into an array, with the array being made up of the arguments that come after the index argument. The index argument determines which of the values will be returned. The value is implicitly…
Translate & Replace functions in SQL Server
This article introduces the new TRANSLATE and REPLACE string functions in SQL Server look identical, shifting letters from one to the other. There is a major difference between them, however. Let’s see how close these functions are: SELECT REPLACE (‘Flat_720’, ‘_’, ‘#’); SELECT TRANSLATE (‘Flat_720′,’_’,’#’); Result: Both returns “Flat#720′ Let’s see what documentation says about…
Replace NULL value in datetime column to an empty string – SQL Server
We were stuck for a moment today in class when trying to replace the NULL values in a date column with an empty string since the ISNULL and COALESCE methods return the default date 1900-01-01. Here are the initial queries. SELECT C.iCustomerID, vcCustomerName, ISNULL(OH.iOrderID,0) OrderID, COALESCE(OrderedDate, NULL, ”) OrderedDate, COALESCE(vcDeliveryStatus, NULL, ‘N/A’) AS DeliveryStatus FROM…
SQL Server – Replicate Function
SQL Server “Replicate” Function repeats a character expression to a specified number of times. It is equivalent to or alternative of Oracle’s RPAD function. Let’s’ check how this will be useful to the developers. SELECT LEN(REPLICATE(2, ’12’)) The above query will return 12 as a result due to 2 is called 12 times (222222222222) hence…
NULL in SQL Server
In Structured Query Language, the special identifier NULL is used to denote the absence of a data value from the database. A field with a NULL value has no value at all. As stated, NULL is not equivalent to any number or value. A column can be specified as not null during table creation or…
NOT NULL in SQL Server
In a relational database, a null value is used when a column’s value is absent or ambiguous. A null is not a zero value or an empty string. In other words, It is a special marker used in SQL to denote the absence of a data value from the database. As a value, a restriction,…
Big Data and SQL 