One of the new features of SQL Server 2012: Conversion Functions

In SQL Server 2012, several conversion functions have been added to support forced conversions of data types.

Since the main use of SQL Server 2008R2, the company's projects to improve development efficiency, many table columns are varchar type, but also store numbers.

When these columns are aggregated, such as sum, to first cast to the numeric type, often error, now with these new functions, you can try first, by judging whether the return value is NULL, we can determine whether the field value is a number, if not a number, directly filtered to, So these functions are very practical and hope to be useful to everyone.

1. Parse function
Syntax: PARSE (string_value as data_type [USING culture])
Parameters:
A String_value:nvarchar (4000) value that represents the formatted value to resolve to the specified data type.
String_value: Must be a representation of the requested data type, otherwise PARSE throws an error.
Data_type: The text value that represents the requested data type of the result.
Culture: An optional string that identifies the culture to which string_value is formatted.
If the culture parameter is not provided, the language of the current session is used. You can use the SET LANGUAGE statement to set the language implicitly or explicitlyStatements were Culture accepts any culture supported by the. NET Framework; It is not limited to languages that are explicitly supported by SQL Server. IfThe culture parameter is invalid and PARSE throws an error.

Example:

--Parse to date Select Parse (' October 11, 2010 ' as DateTime2 USING ' ZH-CN ')/*2010-10-11 00:00:00.0000000*/--parse to USD Select Parse (' $ 345.98 ' as Money USING ' en-us ')/*345.98*/--first sets the culture to be formatted, and then resolves to the date set LANGUAGE ' Simplified Chinese '; SELECT PARSE (' October 11, 2010 ' as datetime)/*2010-10-11 00:00:00.000*/

2. Try_cast function
Returns the value converted to the specified data type (if the conversion succeeds), otherwise returns NULL.
Can be used to determine whether the specified value can be converted to the appropriate data type, indirectly to know what the value is the data type.
Syntax: try_cast (expression as data_type [(length)])
Parameters:
Expression: the value to convert. Any valid expression.
Data_type: The data type to which you want to convert expression.
Length: An optional integer specifying the length of the target data type. The range of acceptable values is determined by the value of data_type.

Example:

--Conversion Failure Select Case when     try_cast ('. ') As numeric (10,2)) is a NULL then               ' conversion failed ' ELSE ' conversion succeeded ' end--conversion succeeded ' Select Case when     try_cast (' 1 ' as numeric (10,2) ') is null< C3/>then ' conversion failed '          ELSE ' conversion succeeded '    END

3. Try_convert function
Returns the value converted to the specified data type (if the conversion succeeds), otherwise returns NULL.
Can be used to determine whether the specified value can be converted to the appropriate data type, indirectly to know what the value is the data type.
Syntax: Try_convert (data_type [(length)], expression [, style])
Parameters:
data_type [(length)]: The data type to which you want to convert expression.
Expression: the value to convert.
Style: An optional integer expression that specifies how the Try_convert function converts expression. The style accepts the same value as the style parameter of the CONVERT function. The range of acceptable values is determined by the value of data_type. If the style is NULL, TRY_CONVERT returns NULL.


Example:

--function Direct error, because there is a problem in the writing of the function Select Case when     try_convert (XML, 4) was NULL then               ' conversion failed '          ELSE ' conversion succeeded '    end/* message 529, Level 16, State 2, line 2nd does not allow explicit conversions from data type int to XML. */

--Conversion Failed Select Case when     try_convert (float, ' a ') was NULL then               ' conversion failed '          ELSE ' conversion succeeded '    end--conversion succeeded Select     Case when Try_convert (datetime, ' 20141201 13:12:11 ') was NULL then               ' conversion failed '         ELSE convert (datetime, ' 20141201 13:12 : One ')   --conversion succeeded    Endselect case when     try_convert (numeric (10,2), ' 1 ') was NULL then               ' conversion failed '         ELSE CONVERT (Numeric (10,2), ' 1 ')     END

4. Try_parse function
In SQL Server 2012, returns the result of an expression (converted to the requested data type), or Null if the cast fails.
Try_parse is used only to convert from string to date/time and number type.
Syntax: Try_parse (string_value as data_type [USING culture])
Parameters:
A String_value:nvarchar (4000) value that represents the formatted value to resolve to the specified data type.
String_value: Must be a valid representation of the requested data type, otherwise try_parse will return Null.
Data_type: Text representing the requested data type of the result.
Culture: An optional string that identifies the culture to which string_value is formatted.
If the culture parameter is not provided, the language of the current session is used. You can use the SET LANGUAGE statement to set this language implicitly or explicitly. Culture accepts any culture supported by the. NET Framework; It is not limited to languages that are explicitly supported by SQL Server. If The culture parameter is not valid, PARSE throws an error.

Example:

SELECT case when    PARSE (' October 11, 2010 ' as DateTime2 USING ' ZH-CN ') was NULL then              ' failed '         ELSE ' success '    END ' conversion result ', P Arse (' October 11, 2010 ' as DateTime2 USING ' zh-cn ') ' converted value '/* conversion result converted value succeeded 2010-10-11 00:00:00.0000000*/

One of the new features of SQL Server 2012: Conversion Functions