The cast () function in SQL

Source: Internet
Author: User

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.

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 a data type conversion is made with the cast function, it can be accepted in the following cases:
(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:

The parameter of the (1), 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), the CAST () function, and the CONVERT () function do not 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

The default values for (5), 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

Instance:

Select  from where softid=8decimal(9,20,1

Select Max (CAST (version as DECIMAL (9,2fromwhere softid= 8

The cast () function in SQL

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.