The SQL TRY_PARSE is one of the SQL Conversions Function, which is used to convert the String data to requested data type, and returns the result as an expression. I suggest you to use this SQL TRY_PARSE function to convert the string data to either Date/time, or Number type. In this article we will show you, How to use/write TRY_PARSE in SQL Server 2014 with example.
SQL TRY_PARSE Function Syntax
The basic syntax of the SQL Server TRY_PARSE Function is as shown below:
|
1 2 3 4 5 |
TRY_PARSE (String_Value AS Data_Type [USING Culture]) -- For example SELECT TRY_PARSE (String_Column AS Data_Type USING 'en-US') AS [Column1] FROM [Source] |
- String_Value: Please specify the valid string that you want to convert as the desired data type.
- Data_Type: Here you have to specify the Data Type to which you want to convert the String_Value
- Culture: This is an optional parameter. And if you ignore parameter, then the function will use the language of the current session.
TIP: If the Try_Parse function is unable to convert the string into desired data type, or if we pass the string as non-convertible then this function will return NULL.
SQL TRY_PARSE Function Example 1
The SQL Server Try_Parse Function is mainly used to convert the string values into date and time, and numeric values. The following query will show multiple ways to use this function.
T-SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- SQL TRY_PARSE FUNCTION Example DECLARE @str AS VARCHAR(50) SET @str = '1234' SELECT TRY_PARSE(@str AS INT) AS Result; -- Direct Inputs SELECT TRY_PARSE('4455' AS DECIMAL(10, 2)) AS Result; SELECT TRY_PARSE('03/03/2017' AS DATETIME) AS Result; SELECT TRY_PARSE('03/03/2017' AS DATETIME2) AS Result; SELECT TRY_PARSE('Tutorial Gateway' AS DATETIME USING 'en-US') AS Result; |
OUTPUT
ANALYSIS
Below lines of code is used to declare a variables of type VARCHAR. Next, we assigning the string data ‘1234’.
|
1 2 |
DECLARE @str AS VARCHAR(50) SET @str = '1234' |
From the below statement, you can see that we are converting the string to integer. We also assigned new names to the result as ‘Result’ using ALIAS Column.
|
1 |
SELECT TRY_PARSE(@str AS INT) AS Result; |
In the next line, We used the TRY_PARSE function directly on string, and converting it to decimal value
|
1 |
SELECT TRY_PARSE('4455' AS DECIMAL(10, 2)) AS Result; |
Next, we rae converting the string to Date Time, and date time 2 data type. i suggest you to refer SQL Data Types to understand the data types, and its limitations.
|
1 2 3 |
SELECT TRY_PARSE('03/03/2017' AS DATETIME) AS Result; SELECT TRY_PARSE('03/03/2017' AS DATETIME2) AS Result; |
Lastly we tried to convert the ‘Tutorial Gateway’ string to date time. As you can see it is not possible so, this function is returning NULL as output.
|
1 |
SELECT TRY_PARSE('Tutorial Gateway' AS DATETIME USING 'en-US') AS Result; |
Use IIF with SQL TRY_PARSE
In this example, we will show you, How to use IIF Statement to handle the NULLs returned by the TRY_PARSE. This can be helpful when you are inserting records, and you don’t know whether the column has date and time or not. In this case, you can simply use this function to check, and if not we can replace it with the current date and time.
T-SQL CODE
|
1 2 3 4 5 |
SELECT IIF( TRY_PARSE('Tutorial Gateway' AS DATETIME) IS NULL, GETDATE(), 'False' ) AS Result; |
Here we used the IIF statement along with the IS NULL statement to check whether the result expression is null or not.
OUTPUT
Thank You for Visiting Our Blog
Leave a Reply