I am going to make headway on my series of articles (tidbits) on type conversion functions available in the Transaction SQL (T-SQL) language.
One analogy of a data type conversion is apply the correct action to change the physical form of water (H2O). You can convert water to ice by cooling it. You can convert water to vapor by heating it. However, the chemical composition of water is still the same!
Information is stored in a database as rows in a table. Each field in a table is defined with a certain data type. The original designer of the database schema might not be aware of all potential uses of the stored information. Therefore, it is not uncommon as a developer to convert data from one type to another.
Today, I want to talk about how to use TRY_CAST function which takes a source data value and a target data type as parameters. It returns the the source data value casted to the target data type. If the data type conversion is not allowed or any potential ERROR occurs, the function returns a NULL value.
The same examples are used again for researching this function.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
-- -- Format variables using TRY_CAST() -- -- Famous quote DECLARE @VAR_QUOTE VARCHAR(128) = 'It is the mark of an educated mind to be able to entertain a thought without accepting it - Aristotle'; -- Full quote SELECT @VAR_QUOTE AS FULL_QUOTE; -- Unexpected truncation SELECT TRY_CAST(@VAR_QUOTE AS CHAR(34)) AS SHORT_QUOTE; -- My 64 K number DECLARE @VAR_NUMBER INT = POWER(2, 16) -- Full number SELECT @VAR_NUMBER AS LARGE_NUMBER -- Returns NULL SELECT TRY_CAST(@VAR_NUMBER AS SMALLINT) AS SHORT_NUMBER -- Date as hex numbers DECLARE @VAR_BYTE AS BINARY(4) = 0x20130723; -- Returns a date SELECT TRY_CAST('20130723' as DATE) AS RIGHT_DATE -- Returns NULL SELECT TRY_CAST(@VAR_BYTE as DATE) AS WRONG_DATE |
The output from the TSQL example is shown below.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
output: FULL_QUOTE ----------------------------------------------------- It is the mark of an educated mind to be able to entertain a thought without accepting it - Aristotle SHORT_QUOTE ---------------------------------- It is the mark of an educated mind LARGE_NUMBER ------------ 65536 SHORT_NUMBER ------------ NULL RIGHT_DATE ---------- 2013-07-23 WRONG_DATE ---------- NULL </span> |
Unlike the CAST function, we end up with a NULL values when arithmetic overflow occurs in statement four or an invalid conversion happens in statement six.
In summary, I suggest using the TRY_CAST with the ISNULL function to prevent run time errors in your code.
Next time, I will be talking about the TRY_PARSE function. This function was introduced in SQL Server 2012. Unlike its cousin, invalid casting of data results in a NULL value instead of an ERROR.
