SQL quick bite 02 (Conversion and working with NULLs)

Implicit conversion

- compatible data types can be automatically converted

Explicit conversion

- requires an explicit conversion function
CAST/TRY_CAST
CONVERT / TRY_CONVERT 
PARSE/ TRY_PARSE
STR

CAST and CONVERT are same but favor CONVERT for dates for a particular date format. It can be implicit convert from str to date but the month 

PARSE make it into a number

STR make it into a string

TRY works but trying to convert if it can rather than run into error.

Eg for using converting data types

SELECT CAST(ProductID AS varchar(5)) + ':' + Name AS ProductName
FROM table

or 

SELECT CONVERT(varchar(5), ProductID) + ':' + Name AS ProductName
FROM table

Take note of the difference in syntax requirement to use CAST and CONVERT. In this case, both give the same results. 

Eg for converting dates

SELECT SellStartDate, 
    CONVERT(nvarchar(30), SellStartDate) AS ConvertedDate,
    CONVERT(nvarchar(30), SellStartDate,126) AS ISO08601FormatDate
FROM table;

The first convert changes it as it is while the second column converts according to a format as specified by the code 126 and this particular code is the ISO08601 Format for Dates.

SELECT Name,TRY_CAST (Size AS Integer) AS NumericSize
FROM table

Working with NULLs

NULL represents a missing or unknown value

ANSI behavior for NULL (unknown) values:
the result of any expression containing a NULL value is NULL. 
2+ NULL = NULL
'Mystring:' + NULL = NULL

equality comparisons always return false for NULL values
NULL = NULL returns false
NULL IS NULL returns true

functions to check NULL values
ISNULL (column.variable, value)
-returns values if the column or variable is NULL.

NULLIF(column/variable, value)
-returns NULL if the column or variable is value

COALESCE(column/variable1, column/variable2)
-returns the value of the first non-NULL column or variable in the list
eg if you want to display the email however if it is NULL then to display their telephone no. 

Eg for changing NULL numbers to 0

SELECT Name, ISNULL(TRY_CAST(Size AS Integer),0)  AS NumericSize
FROM table;

Eg for changing NULL strings into blank string

SELECT ProductNumber, ISNULL(Color, ' ') +',' + ISNULL (Size, ' ') AS ProductDetails

Eg for changing Multi color to NULL 

SELECT Name, NULLIF (Color, 'Multi') AS SingleColor