Home » SQL Server » New built-in Conversion functions in SQL Server 2012……..Part 2 of 5

New built-in Conversion functions in SQL Server 2012……..Part 2 of 5


Microsoft SQL Server 2012 introduces 14 new built-in functions. 3 Out 14 explained below.

Conversion functions

· PARSE (Transact-SQL) : PARSE ( string_value AS data_type [ USING culture ] )

string_value : nvarchar(4000) value representing the formatted value to parse into the specified data type. string_value must be a valid representation of the requested data type, or PARSE raises an error.

data_type : Literal value representing the data type requested for the result.

Culture : Optional string that identifies the culture in which string_value is formatted.

Use PARSE only for converting from string to date/time and number types. For general type conversions, continue to use CAST or CONVERT. Keep in mind that there is a certain performance overhead in parsing the string value. PARSE relies on the presence of .the .NET Framework Common Language Runtime (CLR). This function will not be remoted since it depends on the presence of the CLR. Remoting a function that requires the CLR would cause an error on the remote server.

Returns the result of the expression, translated to the requested data type.

· TRY_PARSE (Transact-SQL) : TRY_PARSE ( string_value AS data_type [ USING culture ] )

string_value : nvarchar(4000) value representing the formatted value to parse into the specified data type. string_value must be a valid representation of the requested data type, or TRY_PARSE returns null.

data_type : Literal representing the data type requested for the result.

Culture : Optional string that identifies the culture in which string_value is formatted.

Use TRY_PARSE only for converting from string to date/time and number types. For general type conversions, continue to use CAST or CONVERT. Keep in mind that there is a certain performance overhead in parsing the string value. TRY_PARSE relies on the presence of .the .NET Framework Common Language Runtime (CLR). This function will not be remoted since it depends on the presence of the CLR. Remoting a function that requires the CLR would cause an error on the remote server.

Returns the result of the expression, translated to the requested data type, or null if the cast fails.

· TRY_CONVERT (Transact-SQL) : TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )

data_type [ ( length ) ] : The data type into which to cast expression.

Expression : The value to be cast.

Style : Optional integer expression that specifies how the TRY_CONVERT function is to translate expression.

TRY_CONVERT takes the value passed to it and tries to convert it to the specified data_type. If the cast succeeds, TRY_CONVERT returns the value as the specified data_type; if an error occurs, null is returned. However if you request a conversion that is explicitly not permitted, then TRY_CONVERT fails with an error. TRY_CONVERT is a new keyword in compatibility level 110. This function is capable of being remote to servers that have a version of SQL Server 2012 and above. It will not be remote to servers that have a version below SQL Server 2012.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: