SQL Server TRY_CAST() Function – Understanding TRY_CAST() in SQL with [Examples]

TRY CAST function in SQL belongs to the Conversions category of functions and resembles the CAST() function. Its purpose is to transform an expression from one data type to another. If successful, the result will be the expression in the expected data type, but if it fails, the function will return null.

This blog post will dive into the TRY_CAST() function in SQL Server, its syntax, and how it differs from the CAST() function. We will use a few code examples to make it easy to implement this function in our SQL Server database.

Understanding the TRY_CAST() Function in SQL Server

The TRY_CAST() function in SQL Server is used to convert an expression of one data type to another data type. This function returns null if the conversion fails, which is a significant advantage over the CAST() function.

Syntax of the TRY_CAST() Function

The syntax of the TRY_CAST() function is straightforward.

TRY_CAST(expression AS data_type)

The expression is the value you want to convert, and the data_type is the data type to which you want to convert it.

The Difference Between CAST() and TRY_CAST()

The CAST() and TRY_CAST() functions in SQL Server have much in common. They are both used to convert an expression of one data type to another. However, the TRY_CAST() function has advantages over the CAST() function. The following are few differences between the two functions:

1. The TRY_CAST() function returns null if the conversion fails, while the CAST() function returns an error.
2. The TRY_CAST() function is more flexible than the CAST() function. It can handle a broader range of data types, and it can also handle null values.
3. The TRY_CAST() function is slower than the CAST() function. That is because it has to check for null values and perform additional checks to ensure that the conversion is successful.

Example: TRY_CAST() Function

Now that we understand the TRY_CAST() function’s basics let’s look at some examples.

Example 1: Try to cast a string to an integer

SELECT TRY_CAST('123' AS INT);

In this example, the TRY_CAST() function tries to cast the string ‘123’ to an integer. Since we can convert this string to an integer, the output will be 123.

Example 2: Try to cast a string to a date

SELECT TRY_CAST('2021-08-20' AS DATE);

In this example, the TRY_CAST() function tries to cast the string ‘2021-08-20’ to a date. Since this string is in the correct date format, the output will be 2021-08-20.

Example 3: Try to cast a string to a float

SELECT TRY_CAST('12.34' AS FLOAT);

In this example, the TRY_CAST() function tries to cast the string ‘12.34’ to a float. Since we can convert this string to a float, the output will be 12.34.

Example 4: Try to cast a string to an integer but with a value that cannot converted

SELECT TRY_CAST('abc' AS INT);

In this example, the TRY_CAST() function tries to cast the string ‘abc’ to an integer. Since we cannot convert this string to an integer, the output will be NULL.

Example 5: Try to cast a string to date but with a value that cannot be converted

SELECT TRY_CAST('2021-08-20 13:00:00' AS DATE);

In this example, the TRY_CAST() function tries to cast the string ‘2021-08-20 13:00:00’ to a date. Since this string is not in the correct date format, the output will be NULL.

Example 6: TRY_CAST returns null

The following example will demonstrate that TRY_CAST() function returns null when the cast fails.

SELECT
CASE WHEN TRY_CAST('mytext' AS float) IS NULL
THEN 'Cast failed'
ELSE 'Cast succeeded'
END AS Result;

The above SQL query will return the following result set.

Result  
----------- 
Cast failed

These examples demonstrate how the TRY_CAST() function can safely convert data types in SQL Server, even when the input data is not in the expected format.

Summary:

In this post, we explored the SQL Server TRY_CAST() function and its usage. TRY_CAST() is a built-in function in SQL Server that is used to convert an expression of one data type to another safely.
It is similar to the CAST() function. Still, it has the added benefit of returning NULL if the cast fails rather than throwing an error.

We also discussed the key differences between the CAST() and TRY_CAST() functions and how we can use them to optimize SQL Server queries. The post includes examples of TRY_CAST() in action, explanations, and code snippets. Using the TRY_CAST() function, we can ensure that data is converted safely without causing errors or unexpected results.

FAQs

The following are frequently asked questions and answers about the TRY_CAST() Function in SQL Server.

References: MSDN-TRY_CAST() IN SQL

You might want to read this too:

• Having VS Where Clause in SQL
• Types of Joins in SQL Server
• Function vs Stored procedure in SQL Server
• Stored Procedure in SQL Server With Examples
• Create, Alter, and Drop Database In SQL Server
• SQL Comparison Operators (Less than, Greater than, Equal, Not Equal operators)
• Primary Key Constraint In SQL Server
• View In SQL | Types Of Views In SQL Server
• SQL Server Trigger Update, Insert, Delete Examples
• SQL pivot tables
• Different ways to delete duplicate rows in SQL Server
• UNION vs UNION ALL in SQL SERVER
• SQL Server Try Catch: Error Handling in SQL Server With [Examples]
• Group by Month in SQL

Let others know about this post by sharing it and leaving your thoughts in the comments section.

• Author
• Recent Posts

Shekh Ali

Meet Sheikh Ali, a skilled Software engineer and passionate a blogger. He enjoys sharing his expertise in C#, Java, SQL, Design Patterns, and other related topics through his writing. When he's not coding, Sheikh Ali can often indulge in his passions for reading books and fitness training.

Latest posts by Shekh Ali (see all)

• Difference Between Array And ArrayList In C#: Choosing the Right Collection - May 28, 2024
• C# Program to Capitalize the First Character of Each Word in a String - February 21, 2024
• C# Program to Find the Longest Word in a String - February 19, 2024