SQL Server 2012 – TRY_CONVERT – New T-SQL Function with Example

With the release of sql server 2012, some new features of T-SQL have been introduced to improve programming in sql server. Some of them are listed below with few examples to demonstrate how it works.

TRY_CONVERT Function

This is new function (TRY_CONVERT) has been introduced in sql server 2012. This function will return a NULL value if any error encounter while conversion. Let’s go through with an example.
Here, you are using CONVERT function in SELECT statement to convert the string “Hello World” to the VARCHAR data type
SELECT CONVERT (VARCHAR(12), ‘Hello World’)

As expected, the SELECT statement returns Hello World when conversion is successful. Now in this example, the SELECT statement tries to perform the same conversion by using the TRY_CONVERT function

Example

SELECT TRY_CONVERT(VARCHAR(12), ‘Hello world’)

This will return same output as earlier function does. But, suppose you try to convert the word World to the INT data type as shown in the below example:

SELECT CONVERT(INT, ‘Hello World’)

As expected, the select statement will throw error message stating that the conversion failed. Now if you try to use TRY_CONVERT function instead of CONVERT as shown in the following example, the statement will return null.

SELECT TRY_CONVERT(INT, ‘Hello World’)

You can use the TRY_CONVERT function to check whether the conversion will fail and then use that result to control your logic flow.
Another new function has been introduced in sql server 2012 that is FORMAT function.

 

Leave a Reply

  

  

  


5 + 2 =