The cast () function in SQL

First, the 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.

If you attempt an impossible conversion (for example, converting a char expression with a letter to an int type), Sqserver displays an error message.

If the length of the data type is not specified at the time of conversion, Sqserver automatically provides a length of 30.

Second, the matters needing attention:

(1). 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 (' + ' as int)

(2). The return value is an integer value of 12. What happens if you try to convert a string that represents a decimal to an integer value?

SELECT CAST (' 12.5 ' as int)

(3). Neither the CAST () function nor the CONVERT () function can perform rounding or truncation operations. Since 12.5 cannot be represented by an int data type, a call to this function 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.

(4). 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

(5). 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: 12

The cast () function in SQL