SQL Cast () function

SQL cast () function 2010-09-17 13:30:26| Category: SQL | Tags: SQL case () function | font size Big Small subscription (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 converts the text string ' 12 ' to an integer: SELECT CAST (' two ' 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 result in an error: Server:msg 245, Level 1, State 1 Syntax error converting the varchar value ' 12.5 ' to a column of data type int. (4). In order 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. The SELECT CAST (' 12.5 ' as Decimal (9,2)) Decimal data type displays the valid 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. The result of SELECT CAST (' 12.5 ' as Decimal) is an integer value: 12 (6). It is easy to convert data types in the table's data. The following example uses the Product table and first executes the following query: SELECT productnumber, ProductLine, ProductModelID from Production.Product Productsubcategoryid < 4 (7). Assume that the product manager has created a system for a unique landmarkTo track the model, type and category of each bicycle produced. He decided to merge the product number, product line identifier, product model identifier and a sequential number to create a unique serial number for each bike produced. In the first step of this process, he asks for the root identifier of all possible products that include all attributes except the sequence number. If you use the following expression, you will not get the desired result, as shown in 6-2. 1.SELECT productnumber 2.+ '-' 3.+ ProductLine 4.+ '-' 5.+ productmodelid as Bikeserialnum 6.FROM production.product 7.WHE RE Productsubcategoryid < 4 we didn't get the result of the hope, and got a little strange error message: Please convert the nvarchar value to int. Because we didn't ask for any conversion before, it was a strange mistake. The problem with this query is that we try to concatenate the character value ProductNumber with the first connector, connect another character value productline with the second connector, and finally connect the ProductModelID character value (which is an integer). The query engine treats the connector as a mathematical operator rather than as a character. Regardless of the outcome, the expression needs to be corrected to ensure that the correct data type is used. The following expression performs the necessary type conversion, returning the result shown in 6-3: SELECT productnumber + '-' + ProductLine + '-' + CAST (ProductModelID as char (4)) as Bikeserial Num from Production.Product WHERE Productsubcategoryid < 4 If you convert an integer value to a character type, no extra spaces will be added. The query engine combines these values with a plus and a connector to perform string join operations instead of adding or subtracting from the preceding values.

SQL Cast () function