PARSE() function in SQL Server

Here you will learn about PARSE() function in SQL Server.

I am going to explain PARSE() function in SQL Server. PARSE() function can convert any string value to Numeric or Date/Time format. If passed string value cannot be converted to Numeric or Date/Time format, it will result to an error. PARSE() function relies on Common Language Runtime to convert the string value. If there is no CLR installed on the server, PARSE() function will return an error. Additionally, please note that PARSE only works for String Values to be converted to Numeric and Date/Time. If you need any other data type to be converted in to another data type, you may use traditional CAST or Convert Function. As a generic rule, there is always a bit performance overhead when any string value is parsed.

SELECT PARSE('757.000' AS INT) AS ValueInt

This will return result as 757.

SELECT PARSE('Oct 20, 2011' AS DATETIME)
AS ValueDT
 

This will return result as 2011-10-20 00:00:00.000.USE Test
GO
SELECT SP.[StateCode], PARSE(A.[Code] AS INT) ZIPCode
             FROM [Person].[Address] A
             INNER JOIN [Person].[State] SP
             ON SP.StateID = A.StateID
             WHERE SP.[CountryCode] = 'IN'  AND LEN(A.[ZIPCode]) <=6
GO

This will return result as

StateCode          ZIPCode
MH                      314001
RJ                       354008
UP                      315404
MP                      331403
AP                      411045
KA                      211012
PJ                       452001
JK                       311012
HP                     110101


But problem is that , "What happens if the string which we had like to convert to INT cannot be converted to INT because it contains alphabets onto it? Well, when this happens, PARSE will throw an error. In this condition we can use TRY_PARSE() Function in SQL Server.

The TRY_PARSE() function can convert any string value to Numeric or Date/Time format. If the passed string value cannot be converted to Numeric or Date/Time format, it will result to a NULL.

As well as The PARSE() function relies on Common Language Runtime to convert the string value. If there is no CLR installed in the server, the TRY_PARSE() function will also return an error. Additionally, please note that TRY_PARSE() only works for String Values to be converted to Numeric and Date/Time. If you need any other data type to be converted in to another data type, you may use the traditional CAST or the Convert Function. As a generic rule, there is always a bit improvement in the performance when any string value is parsed.