Introduction to the cast () function in SQL

The cast function is used to explicitly convert an expression of a data type to another data type. The argument to the CAST () function is an expression that includes the source value and the target data type separated by the AS keyword.
Syntax:
CAST (expression as data_type)

Parameter description:

expression: any valid sqserver expression.

as: used to separate two parameters, before as is the data to be processed, after as is the type of data to be converted.

data_type: The data types provided by the target system, including bigint and sql_variant, cannot be used with user-defined data types.

When using the CAST function for data type conversions, it can be accepted in the following situations:

(1) The data type of the two expressions is exactly the same.
(2) Two expressions can be implicitly converted.
(3) The data type must be explicitly converted.

The argument to the CAST () function is an expression that includes the source value and the target data type separated by the AS keyword. The following example is used to convert the text string ' 12 ' to an integral type:

SELECT CAST (' n ' as int) from DUAL;

The return value is an integer value of 12. 650) this.width=650; "id=" aimg_279 "src=" http://bbs.delit.cn/data/attachment/forum/201510/22/ 213935s6der1oz1dg1dn19.png.thumb.jpg "alt=" 213935s6der1oz1dg1dn19.png.thumb.jpg "/>

What happens if you try to convert a string that represents a decimal to an integer value?

SELECT CAST (' 12.5 ' as int) from DUAL;

650) this.width=650; "Src=" http://bbs.delit.cn/data/attachment/forum/201510/22/ 214036nc78e3ccmvvwycce.png.thumb.jpg "alt=" 214036nc78e3ccmvvwycce.png.thumb.jpg "/>

But,sqlserver will produce an error:
server:msg 245, Level A, State 1, line 1
Syntax Error converting the varchar value
' 12.5 ' to a column of data type int.
Cause: The CAST () function cannot perform rounding or truncation operations.

then to return a valid value, you must use a data type that can handle the value. For this example, there are multiple data types available. If you convert this value to a decimal type by using the cast () function, you first need to define the precision and scale of the decimal value. In this example, the precision and scale are 9 and 2, respectively. The precision is the total number of digits, including the sum of the left and right digits of the decimal point. The number of decimal places is the right-hand digit. This means that the largest integer value that this example can support is 9999999, and the smallest decimal number is 0.01.
SELECT CAST (' 12.5 ' as Decimal (9,2))
The Decimal data type displays significant decimal digits in the result grid: 12.50

the default values for precision and scale are 18 and 0, respectively. If these two values are not provided in the decimal type, SQL Server truncates the decimal portion of the number without generating an error.

SELECT CAST (' 12.5 ' as decimal)
The result is an integer value:

Reprint please specify the source:

Author: metric Technology http://www.delit.cn

Introduction to the cast () function in SQL