In SQL Server Denali, there are three new conversion functions being introduced, namely:
Today we will quickly look at PARSE() function. 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 (CLR) 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.
Now let us quickly look at examples on how PARSE() works:
Example1: Converting String to INT
SELECT PARSE('100.000' AS INT) AS ValueInt
This will return result as 100.
Example2: Converting String to Date/Time
SELECT PARSE('July 30, 2011' AS DATETIME)
This will return result as 2011-07-30 00:00:00.000.
Example3: Converting String to INT from Table
You can clearly see that the PostalCode column in the database is in the format of the nvarchar. We can use PARSE to convert it to INT.
SELECT SP.[StateProvinceCode] ,PARSE(A.[PostalCode] AS INT) PostCode
FROM [Person].[Address] A
INNER JOIN [Person].[StateProvince] SP
ON SP.StateProvinceID = A.StateProvinceID
WHERE SP.[CountryRegionCode] = 'US'
AND LEN(A.[PostalCode]) <=6
Now the question is, “What happens if the string which we’d 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 the next blog post, we will see the usage of TRY_PARSE(), where we will try to answer the same question in detail.
Reference: Pinal Dave (https://blog.sqlauthority.com)