SQL PARSE Function

The SQL PARSE function is a SQL Conversions Function used to convert the String data to the 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.

SQL PARSE Function Syntax

The syntax of the SQL Server PARSE Function is as shown below:

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]

• Data_Type: Data Type to which you want to convert the String_Value
• Culture: This is an optional parameter. By default, it uses the current session language.

TIP: If the Parse function is unable to convert the string into the desired data type. Or if we pass the non-convertible string, or if we pass NULL value, then this Parse 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 Parse function query parses integer or string to decimal and string to DateTime.

-- 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

We are converting the string value to an integer and used ALIAS Column to name it as ‘Result’.

SELECT TRY_PARSE(@str AS INT) AS Result;

In the next line, We used the SQL PARSE function directly on string value and converting it to decimal value with precision 2

SELECT PARSE('1234' AS DECIMAL(10, 2)) AS Result;

Next, we are converting the string to DateTime and datetime2 data type.

SELECT PARSE('06/03/2017' AS DATETIME) AS Result;  

SELECT PARSE('06/03/2017' AS DATETIME2) AS Result;

PARSE Function Example 2

In this Parse function example, we will work with NULL values and non-convertible strings.

-- SQL PARSE FUNCTION Example 
DECLARE @strval AS VARCHAR(50)
SET @strval = NULL

SELECT PARSE(@strval AS INT) AS Result;

OUTPUT

Let us use what happens if we pass the NULL value as the direct input

SELECT PARSE(NULL AS INT) AS Result;

We tried to convert the ‘Tutorial Gateway’ string to date time. It is not possible, so this parse function is returning Error as output.

SELECT PARSE('Tutorial Gateway' AS DATETIME USING 'en-US') AS Result;