2023年11月27日发(作者：)
tryparse_了解SQLServer的TRY_PARSE和TRY_CONVERT
函数
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.
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
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, 3 and 5 record has age in text, while 2 and 4 records has age in numbers.
strdthndth
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:
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:
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:
使⽤TRY_PARSE进⾏转换 (Conversion using TRY_PARSE)
SELECT TRY_PARSE('
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），⽽后
者则不依赖于。
发布评论