I am going to bring closure to my series of very short articles 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 the TRY_PARSE function which takes a source string, a target data type, and a optional culture information as parameters. It returns the target data type containing the transformed source string.
If the data type conversion is not allowed or any potential ERROR occurs, the function returns a NULL value.
The same top two statements are used again for researching this function.
|
1 2 3 4 5 6 7 8 9 |
-- -- Convert strings w/ culture info 2 data types using TRY_PARSE() -- -- Passes since right culture SELECT TRY_PARSE('£45.99' AS MONEY USING 'en-GB') AS COST_OF_TOY1; -- Returns NULL since wrong culture SELECT TRY_PARSE('£45.99' AS MONEY USING 'en-US') AS COST_OF_TOY2; |
The output from the TSQL example is shown below.
|
1 2 3 4 5 6 7 8 9 |
output: COST_OF_TOY1 --------------------- 45.99 COST_OF_TOY2 --------------------- NULL |
To recap, the TRY_PARSE function is used to convert strings to other data types. Cultural specific information for money, date, or time formatting can be accounted for.
Unlike the PARSE function, we end up with a NULL values when an invalid conversion happens in statement two.
In summary, I suggest try using the TRY_PARSE with the ISNULL function to prevent run time errors in your code.
