SQL - PARSE() Function

The SQL PARSE() function is a conversions function that converts String data to the desired data format and returns the outcome as an expression. It is advised to utilize this SQL PARSE function to change the string data into a Date/Time or Numeric type.

Syntax

Following is the syntax of the SQL PARSE() function −

PARSE ( string_value AS data_type [ USING culture ] )

Parameters

This function accepts only three parameter. The same is described below −

  • string_value − It is the expression that we want to change to a specific data type.

  • data_type − It is the datatype that we want to convert the string_value.

  • culture − It is an optional parameter for which the cukture of string value is formatted.

Let’s look into the cultures supported by the parse in the below table −

CULTURE FULLNAMEA LANGUAGUE
en-US us_english English
de-DE Deutsch German
fr-FR Français French
ja-JP 日本語 Japanese
da-DK Dansk Danish
es-ES Español Spanish
it-IT Italiano Italian
nl-NL Nederlands Dutch
nn-NO Norsk Norwegian
pt-PT Português Portuguese
fi-FI Suomi Finnish
sv-SE Svenska Swedish
Cs-CZ čeština Czech
Hu-HU magyar Hungarian
Pl-PL polski Polish
Ro-RO română Romanian
hr-HR hrvatski Croatian
Sk-SK slovenčina Slovak
Sl-SI slovenski Slovenian
El-GR ελληνικά Greek
bg-BG български Bulgarian
Ru-RU русский Russian
Tr-TR Türkçe Turkish
en-GB British British English
Et-EE eesti Estonian
lv-LV latviešu Latvian
lt-LT lietuvių Lithuanian
pt-BR Português (Brasil) Brazilian
zh-TW 繁體中文 Traditional Chinese
Ko-KR 한국어 Korean
zh-CN 简体中文 Simplified Chinese
ar-SA Arabic Arabic
Th-TH ไทย Thai

Example

Let us try to PARSE the string into datetime2 by using the following query −

SELECT PARSE('THURSDAY, 23 february 2023' AS datetime2 USING 'zh-CN') AS Result;

Output

When we execute the above query, the output is obtained as follows −

+------------------------------------+
|                              Result|
+------------------------------------+
|        2023-02-23 00:00:00.0000000 |
+------------------------------------+

Example

Let's look into the another scenario to PARSE the string into datetime by using the following query −

SELECT PARSE('02/23/2023' AS DATETIME) AS Result;

Output

When we execute the above query, the output is obtained as follows −

+------------------------------------+
|                              Result|
+------------------------------------+
|           2023-02-23 00:00:00.000  |
+------------------------------------+

Example

Let's try to prase the string into int by using the following query −

SELECT PARSE('123.000' AS INT) AS Result;

Output

On executing the above query, the output is displayed as follows −

+------------------------------------+
|                              Result|
+------------------------------------+
|                                123 |
+------------------------------------+

Example

Considering the following example, where we are going to parse string to numeric by running the following query −

DECLARE @string AS VARCHAR(9)
SET @string = '56789'
SELECT PARSE(@string AS DECIMAL(8, 2)) AS decimalresult;
SELECT PARSE(@string AS MONEY) AS moneyresult;

Output

On executing the above query, the output is displayed as follows −

+-------------------+
|     decimalresult |
+-------------------+
|        56789.00   |
+-------------------+
+-------------------+
|       moneyresult |
+-------------------+
|       56789.00    |
+-------------------+

Example

Let's look into the following example, where we are going to prase with NULL values by running the following query −

SELECT PARSE(NULL AS INT) AS Result;

Output

The output for the above query is produced as given below − 

Argument data type NULL is invalid for argument 1 of parse function.

Example

Look at the other scenario, where we are passing NULL as a parameter and checking the result by using the following query −

DECLARE @txt AS VARCHAR(22);
SET @txt = NULL
SELECT PARSE(@txt AS INT) AS Result;

Output

If we compile and run the above query, the result is produced as follows −

+------------------------------------+
|                              Result|
+------------------------------------+
|                               NULL |
+------------------------------------+

Example

let's look into the following example, where parse() function to parse string to date using the following query −

SELECT PARSE('February/23/2023' AS date) AS Result;

Output

On executing the above query, it will generate the following output as shown below −

+------------------------------------+
|                              Result|
+------------------------------------+
|                         2023-02-23 |
+------------------------------------+

Example

Let’s consider another example, where we are going to use the parse to convert to numeric types by using the following query −

SELECT PARSE('1,2,3,4,5' AS int) As Result;

Output

When the query gets executed, it will generate the output as shown below −

+------------------------------------+
|                              Result|
+------------------------------------+
|                              12345 |
+------------------------------------+

Example

Considering the following example, where we are using the parse with different culture and checking the result by using the following query −

SELECT PARSE('30,000' AS decimal(8,2) USING 'en-US') AS inEnglish ;
SELECT PARSE('30,000' AS decimal(8,2) USING 'fr-FR') AS inFrench;

Output

When we execute the above query, the output is obtained as follows −

+-------------------+
|         inEnglish |
+-------------------+
|        30000.00   |
+-------------------+
+-------------------+
|          inFrench |
+-------------------+
|           30.00   |
+-------------------+
sql-conversion-functions.htm
Advertisements