Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

TRY_PARSE function in SQL Server 2012

Suggested Videos
Part 120 - Reverse PIVOT table in SQL Server
Part 121 - Choose function in SQL Server
Part 122 - IIF function in SQL Server



In this video we will discuss 
  • TRY_PARSE function
  • Difference between PARSE and TRY_PARSE functions


TRY_PARSE function
  • Introduced in SQL Server 2012
  • Converts a string to Date/Time or Numeric type
  • Returns NULL if the provided string cannot be converted to the specified data type
  • Requires .NET Framework Common Language Runtime (CLR)
Syntax : TRY_PARSE ( string_value AS data_type )

Example : Convert string to INT. As the string can be converted to INT, the result will be 99 as expected.

SELECT TRY_PARSE('99' AS INT) AS Result

Output : 
try_parse function in sql server 2012

Example : Convert string to INT. The string cannot be converted to INT, so TRY_PARSE returns NULL

SELECT TRY_PARSE('ABC' AS INT) AS Result


Output : 
sql server tryparse

Use CASE statement or IIF function to provide a meaningful error message instead of NULL when the conversion fails.

Example : Using CASE statement to provide a meaningful error message when the conversion fails.

SELECT
CASE WHEN TRY_PARSE('ABC' AS INT) IS NULL
           THEN 'Conversion Failed'
           ELSE 'Conversion Successful'
END AS Result

Output : As the conversion fails, you will now get a message 'Conversion Failed' instead of NULL
sql server try_parse

Example : Using IIF function to provide a meaningful error message when the conversion fails.

SELECT IIF(TRY_PARSE('ABC' AS INT) IS NULL, 'Conversion Failed',
                 'Conversion Successful') AS Result

What is the difference between PARSE and TRY_PARSE
PARSE will result in an error if the conversion fails, where as TRY_PARSE will return NULL instead of an error. 

Since ABC cannot be converted to INT, PARSE will return an error
SELECT PARSE('ABC' AS INT) AS Result

Since ABC cannot be converted to INT, TRY_PARSE will return NULL instead of an error
SELECT TRY_PARSE('ABC' AS INT) AS Result

Example : Using TRY_PARSE() function with table data. We will use the following Employees table for this example.
try_parse in sql server 2012

SQL Script to create Employees table
Create table Employees
(
     Id int primary key identity,
     Name nvarchar(10),
     Age nvarchar(10)
)
Go

Insert into Employees values ('Mark', '40')
Insert into Employees values ('John', '20')
Insert into Employees values ('Amy', 'THIRTY')
Insert into Employees values ('Ben', '21')
Insert into Employees values ('Sara', 'FIFTY')
Insert into Employees values ('David', '25')
Go

The data type of Age column is nvarchar. So string values like (THIRTY, FIFTY ) are also stored. Now, we want to write a query to convert the values in Age column to int and return along with the Employee name. Notice TRY_PARSE function returns NULL for the rows where age cannot be converted to INT.

SELECT Name, TRY_PARSE(Age AS INT) AS Age
FROM Employees

try parse in sql server

If you use PARSE instead of TRY_PARSE, the query fails with an error.

SELECT Name, PARSE(Age AS INT) AS Age
FROM Employees

The above query returns the following error
Error converting string value 'THIRTY' into data type int using culture

1 comment:

  1. Saludo, no hay forma de que pueda dar estos tutoriales tambien en español
    Greeting, there is no way you can give these tutorials also in Spanish

    ReplyDelete

If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.