Tutorial Gateway

SQL PARSE Function

by

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:

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

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

SQL PARSE Function 1

ANALYSIS

Below lines of code is used to declare a variable of VARCHAR type. Next, we assigning the string data ‘11122’.

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.

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

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.

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

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

SELECT PARSE(@strval AS INT) AS Result;

OUTPUT

SQL PARSE Function 2

Let us use what will happen, if we pass the NULL value as the direct input

SELECT PARSE(NULL AS INT) AS Result;

SQL PARSE Function 3

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.

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

SQL PARSE Function 4

Thank You for Visiting Our Blog

Placed Under: SQL