The SQL PARSE is one of the SQL Conversions Function used to convert the String data to requested data type, and returns the result as an expression. It is recommended to use this SQL PARSE function to convert the string data to either Date time, or Number type. In this article we will show you, How to use, or write PARSE in SQL Server with example.
SQL PARSE Function Syntax
The basic syntax of the SQL Server PARSE Function is as shown below:
|
1 2 3 4 5 |
PARSE (String_Value AS Data_Type [USING Culture]) -- For example SELECT PARSE (String_Column_Value AS Data_Type USING 'en-US') AS [result_name] FROM [Source] |
- String_Value: Please specify a valid string that you want to convert into 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. If you forgot this parameter, then the function will use the language of the current session.
TIP: If the Parse function is unable to convert the string into desired data type, or if we pass the non-convertible string, or if we pass NULL value then this function will return Error.
SQL PARSE Function Example 1
The SQL Server Parse Function is mainly used to convert the string 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 |
-- SQL PARSE FUNCTION Example DECLARE @str AS VARCHAR(50) SET @str = '11122' SELECT PARSE(@str AS INT) AS Result; -- Direct Inputs SELECT PARSE('1234' AS DECIMAL(10, 2)) AS Result; SELECT PARSE('06/03/2017' AS DATETIME) AS Result; SELECT PARSE('06/03/2017' AS DATETIME2) AS Result; |
OUTPUT
ANALYSIS
Below lines of code is used to declare a variable of VARCHAR type. Next, we assigning the string data ‘11122’.
|
1 2 |
DECLARE @str AS VARCHAR(50) SET @str = '11122' |
From the below statement, you can see that we are converting the string value 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 PARSE function directly on string value, and converting it to decimal value with precision 2
|
1 |
SELECT PARSE('1234' 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 PARSE('06/03/2017' AS DATETIME) AS Result; SELECT PARSE('06/03/2017' AS DATETIME2) AS Result; |
SQL PARSE Function Example 2
In this example, we will work with NULL values, and non-convertible strings.
T-SQL CODE
|
1 2 3 4 5 |
-- SQL PARSE FUNCTION Example DECLARE @strval AS VARCHAR(50) SET @strval = NULL SELECT PARSE(@strval AS INT) AS Result; |
OUTPUT
Let us use what will happen, if we pass the NULL value as the direct input
|
1 |
SELECT PARSE(NULL AS INT) 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 Error as output.
|
1 |
SELECT PARSE('Tutorial Gateway' AS DATETIME USING 'en-US') AS Result; |
Thank You for Visiting Our Blog
Leave a Reply