try parse_了解SQL Server的TRY_PARSE和TRY

try parse

Data conversion is one of the most fundamental tasks of any programming language. Data received from different sources is often not in the right format. For example, if you receive an XML file where age is in the string format and you want to calculate an average age for the people in the file you will need to convert age into an integer.

数据转换是任何编程语言中最基本的任务之一。从不同来源收到的数据通常格式不正确。例如，如果您收到一个XML文件，其中age是字符串格式，并且想要计算文件中人员的平均年龄，则需要将age转换为整数。

To make the conversion process simple, the TRY_PARSE and TRY_CONVERT functions were introduced in SQL Server 2012. Before TRY_PARSE and TRY_CONVERT, SQL Server only had the PARSE and CONVERT functions.

为了简化转换过程，SQL Server 2012中引入了TRY_PARSE和TRY_CONVERT函数。在TRY_PARSE和TRY_CONVERT之前，SQL Server仅具有PARSE和CONVERT函数。

In this article, we will look at what the TRY_PARSE and TRY_CONVERT functions are; how they differ from the PARSE and CONVERT functions, and how they differ from each other. I find this is often quicker in SQL than in Excel Power BI.

在本文中，我们将看看TRY_PARSE和TRY_CONVERT函数是什么；它们与PARSE和CONVERT函数有何不同，以及它们之间有何不同。我发现在SQL中这通常比在Excel Power BI中更快。

TRY_PARSE (TRY_PARSE)

The TRY_Parse function is used to convert string data into numeric or date data types. It returns NULL if the conversion is not possible. Let’s see an example.

TRY_Parse函数用于将字符串数据转换为数字或日期数据类型。如果无法进行转换，则返回NULL。让我们来看一个例子。

SELECT TRY_PARSE('100' as INT) AS OUTPUT

In the above script, the TRY_PARSE function will attempt to convert the string ‘100’ into the integer hundred. Since this conversion is possible (because 100 can be converted into an integer) the OUTPUT will be the integer 100 as shown below:

在上面的脚本中，TRY_PARSE函数将尝试将字符串“ 100”转换为整数百。由于这种转换是可能的（因为100可以转换为整数），所以OUTPUT将是整数100，如下所示：

Now let’s try to convert the string ‘XYZ’ into an integer using TRY_PARSE. Clearly, XYZ is not an integer. In this case, TRY_PARSE will return null. Try the following script:

现在，让我们尝试使用TRY_PARSE将字符串“ XYZ”转换为整数。显然，XYZ不是整数。在这种情况下，TRY_PARSE将返回null。尝试以下脚本：

SELECT TRY_PARSE('XYZ' as INT) AS OUTPUT

The output will look like this:

输出将如下所示：

You can use CASE or IIF statements to handle scenarios where conversion cannot be achieved. You can display an appropriate message to the users letting them know whether the conversion was successful or not. The following script is an example of how this works.

您可以使用CASE或IIF语句来处理无法实现转换的方案。您可以向用户显示一条适当的消息，让他们知道转换是否成功。以下脚本是其工作方式的示例。

SELECT 
CASE WHEN TRY_PARSE('XYZ' as INT) IS NULL
  THEN 'Sorry, conversion unsuccessful'
  ELSE 'Data converted successfully'
END AS OUTPUT

Again the TRY_PARSE statement is being used to convert ‘XYZ’ to number however, this time the CASE statement has been used to check if the result of the conversion is NULL. If the result is NULL, then a message ‘’Sorry, conversion unsuccessful’’ is printed on the console. If conversion is successful and TRY_PARSE doesn’t return false, the statement ‘Data converted successfully’ is shown in the console. In this case, TRY_PARSE returns NULL, therefore the output looks like this:

再次使用TRY_PARSE语句将“ XYZ”转换为数字，但是这次使用CASE语句检查转换结果是否为NULL。如果结果为NULL，则在控制台上显示消息“对不起，转换失败”。如果转换成功并且TRY_PARSE没有返回false，则在控制台中显示“数据转换成功”语句。在这种情况下，TRY_PARSE返回NULL，因此输出如下所示：

TRY_PARSE和PARSE之间的区别 (Difference between TRY_PARSE and PARSE)

The basic difference between TRY_PARSE and PARSE is that the PARSE function returns an error when the conversion is not possible. The TRY_PARSE function doesn’t throw an error; it just returns NULL when conversion is not possible. This difference becomes very important when you work with tabular data.

TRY_PARSE和PARSE之间的基本区别是，当无法进行转换时，PARSE函数将返回错误。 TRY_PARSE函数不会引发错误；当无法进行转换时，它只会返回NULL。当使用表格数据时，这种差异变得非常重要。

Execute the following script to create dummy data that we are going to use to explain the difference between TRY_PARSE and PARSE functionality.

执行以下脚本来创建伪数据，我们将使用它们来解释TRY_PARSE和PARSE功能之间的区别。

CREATE DATABASE School
GO
 
USE School
GO
 
CREATE TABLE Students
(
  Id INT PRIMARY KEY IDENTITY,
  StudentName VARCHAR (50),
  StudentAge VARCHAR (50)
)
GO
 
INSERT INTO Students VALUES ('Sally', '25' )
INSERT INTO Students VALUES ('Edward', 'Fifty' )
INSERT INTO Students VALUES ('Jon', '30' )
INSERT INTO Students VALUES ('Scot', 'Thirty Five' )
INSERT INTO Students VALUES ('Ben', '37' )

In the script above a database “School” has been created with one table “Students” in it. The Students table has three columns Id, StudentName and StudentAge. The type of StudentAge column has been set to VARCHAR since we want to convert this column into integers using both PARSE and TRY_PARSE. Finally, five records have been inserted into this table. You can see that the 1^st, 3^rd and 5^th record has age in text, while 2^nd and 4^th records has age in numbers.

在上面的脚本中，创建了一个数据库“ School”，其中有一个表“ Students”。学生表具有三列ID，StudentName和StudentAge。 StudentAge列的类型已设置为VARCHAR，因为我们想同时使用PARSE和TRY_PARSE将其转换为整数。最后，五个记录已插入到该表中。你可以看到，第^1，第3 ^和 ^第 5记录有年龄在文本，而2 ^个和^第 4 ^次记录在数字时代。

Let’s first use TRY_PARSE to convert StudentAge column into integer. Execute the following script:

首先让我们使用TRY_PARSE将StudentAge列转换为整数。执行以下脚本：

USE School
SELECT StudentName, TRY_PARSE(StudentAge as INT) as AGE
FROM Students

The output of the above script looks like this:

上面脚本的输出如下所示：

You can see from the output, that TRY_PARSE returned NULL for the StudentAge column of those records where it could not convert the values to integer.

从输出中可以看到，TRY_PARSE为这些记录的StudentAge列返回了NULL，无法将这些值转换为整数。

Now, let’s use PARSE function to see what it returns.

现在，让我们使用PARSE函数来查看返回的内容。

USE School
SELECT StudentName, PARSE(StudentAge as INT) as AGE
FROM Students

This script throws an error because in the second record we have “Fifty” in the StudentAge column and the PARSE function throws an error when it cannot convert a string into an integer. The error will look like this:

该脚本引发错误，因为在第二条记录中，StudentAge列中包含“ Fifty”，并且当PARSE函数无法将字符串转换为整数时，它会引发错误。该错误将如下所示：

TRY_CONVERT (TRY_CONVERT)

The TRY_PARSE function can only convert strings to numeric or date data types. The TRY_CONVERT function can perform conversions between all the data types except those where conversion is explicitly not permitted.

TRY_PARSE函数只能将字符串转换为数字或日期数据类型。 TRY_CONVERT函数可以在所有数据类型之间执行转换，除非明确不允许进行转换。

Like the TRY_PARSE function, TRY_CONVERT returns NULL when conversion is not possible. However, TRY_CONVERT throws an error when we try to perform a conversion that is not explicitly permitted.

与TRY_PARSE函数类似，当无法进行转换时，TRY_CONVERT返回NULL。但是，当我们尝试执行未明确允许的转换时，TRY_CONVERT会引发错误。

Let’s start with a very simple example of TRY_CONVERT:

让我们从一个非常简单的TRY_CONVERT示例开始：

SELECT TRY_CONVERT(INT, '150') AS OUTPUT

In the script above we simply tried to convert the string ‘150’ into an integer. This conversion was successful.

在上面的脚本中，我们只是尝试将字符串“ 150”转换为整数。此转换成功。

Now let’s try to convert ‘XYZ’ into an integer. We know that XYZ cannot be converted into integer; therefore, in this case, TRY_CONVERT will return an error.

现在，让我们尝试将“ XYZ”转换为整数。我们知道XYZ不能转换为整数。因此，在这种情况下，TRY_CONVERT将返回错误。

SELECT TRY_CONVERT(INT, 'XYZ') AS OUTPUT

The above script returns NULL

上面的脚本返回NULL

As mentioned above, there are some conversions that the TRY_CONVERT function cannot perform since they are not permitted. For example, converting an integer into XML is not permitted. In these cases, the TRY_CONVERT function returns an error.

如上所述，由于不允许进行某些转换，因此TRY_CONVERT函数无法执行。例如，不允许将整数转换为XML。在这些情况下，TRY_CONVERT函数将返回错误。

Take a look at the following example.

看下面的例子。

SELECT TRY_CONVERT(XML, 10) AS OUTPUT

Here, in this case, we are trying to convert 10 which is an integer into XML data type. Since this conversion is not permitted, an error will be thrown which looks like this:

在这里，在这种情况下，我们试图将10（一个整数）转换为XML数据类型。由于不允许这种转换，因此将引发如下错误：

You can use the CASE statement with the TRY_CONVERT function as well, in order to display an appropriate message to the user if the conversion fails. Have a look at the script below:

您还可以将CASE语句与TRY_CONVERT函数一起使用，以便在转换失败时向用户显示适当的消息。看下面的脚本：

SELECT 
CASE WHEN TRY_CONVERT(INT, 'XYZ') IS NULL
  THEN 'Data conversion unsuccessful'
  ELSE 'Data converted successfully'
END AS OUTPUT

The output of the script above looks like this:

上面脚本的输出如下所示：

If you replace ‘XYZ’ in the above script with ‘50’, the conversion will be successful and you will see the message ‘Data converted successfully’ in the OUTPUT.

如果将上面脚本中的“ XYZ”替换为“ 50”，则转换将成功，并且在输出中将看到消息“数据转换成功”。

TRY_CONVERT和CONVERT之间的区别 (Difference between TRY_CONVERT and CONVERT)

The difference between TRY_CONVERT and CONVERT is similar to the difference between TRY_PARSE and PARSE.

TRY_CONVERT和CONVERT之间的区别类似于TRY_PARSE和PARSE之间的区别。

The TRY_CONVERT function returns NULL if the conversion is not possible while the CONVERT throws an error.

如果在CONVERT引发错误时无法进行转换，则TRY_CONVERT函数返回NULL。

Let’s see the difference with help of the School database and the Students table that we created earlier.

让我们看看在学校数据库和我们之前创建的“学生”表的帮助下的区别。

Execute the following script to see what TRY_COVERT function returns when you convert the StudentAge column into integer.

执行以下脚本，查看将StudentAge列转换为整数时TRY_COVERT函数返回什么。

USE School
SELECT StudentName, TRY_CONVERT(INT, StudentAge) as AGE
FROM Students

The output of the above script looks like this:

上面脚本的输出如下所示：

You can see from the output, that TRY_CONVERT returned NULL for the StudentAge column of those records where it could not convert the values into an integer.

从输出中可以看到，TRY_CONVERT对于这些记录的StudentAge列返回了NULL，无法将这些值转换为整数。

Now, let’s use CONVERT function to see what it returns.

现在，让我们使用CONVERT函数来查看返回的内容。

USE School
SELECT StudentName, PARSE(StudentAge as INT) as AGE
FROM Students

This script throws an error because in the second record we have “Fifty” in the StudentAge column and PARSE function throws an error when it cannot convert a string into an integer. The error will look like this:

该脚本会引发错误，因为在第二条记录中，StudentAge列中包含“ Fifty”，而当PARSE函数无法将字符串转换为整数时，它会引发错误。该错误将如下所示：

TRY_CONVERT和TRY_PARSE之间的区别 (Difference between TRY_CONVERT and TRY_PARSE)

The difference between TRY_CONVERT and TRY_PARSE is simple. TRY_PARSE can only convert string data type to numeric or date data types while TRY_CONVERT can be used for any general type conversion. Let’s explain this with the help of an example.

TRY_CONVERT和TRY_PARSE之间的区别很简单。 TRY_PARSE只能将字符串数据类型转换为数字或日期数据类型，而TRY_CONVERT可以用于任何常规类型转换。让我们借助示例进行解释。

Let’s try to convert a string into XML data type using both the TRY_CONVERT and the TRY_PARSE functions.

让我们尝试同时使用TRY_CONVERT和TRY_PARSE函数将字符串转换为XML数据类型。

使用TRY_CONVERT进行转换 (Conversion using TRY_CONVERT)

SELECT TRY_CONVERT(XML, '<car><name>Toyota</name></car>') AS Result

The output will look like this:

输出将如下所示：

This means that the conversion was successful.

这意味着转换成功。

Now let’s perform the same conversion using TRY_PARSE.

现在，让我们使用TRY_PARSE执行相同的转换。

使用TRY_PARSE进行转换 (Conversion using TRY_PARSE)

SELECT TRY_PARSE('<car><name>Toyota</name></car>' AS XML) AS Result

Since TRY_PARSE can only convert string data into numeric or date data types, the above script will throw an error that looks like this:

由于TRY_PARSE只能将字符串数据转换为数字或日期数据类型，因此上述脚本将引发如下错误：

A final more technical difference between TRY_PARSE and TRY_CONVERT is that the former depends on the .NET Framework Common Language Runtime (CLR) for its execution while the latter doesn’t.

TRY_PARSE和TRY_CONVERT之间的最后一个更技术上的区别是，前者的执行依赖于.NET Framework公共语言运行时（CLR），而后者则不依赖于。