loading..
Русский    English
05:29
go

Data type conversion and CAST function page 1

Implicit data type conversions are possible in  SQL(Structured Query Language) is a database computer language designed for the retrieval and management of data in relational database management systems (RDBMS), database schema creation and modification, and database object access control management.SQL implementations. For example, if a smallint is compared to an int in T-SQL, the smallint is implicitly converted to int before the comparison proceeds. See  Электронная документация SQL Server Books OnlineBOL (SQL Server Books Online - electronic documentation and tutorials) for details about implicit and explicit conversions in MS SQL Server.

Example 5.9.1

Find the average price of laptops with the prefix text "Average price = ".

If you run the query

Console
Execute
  1. SELECT 'Average price = ' + AVG(price)
  2. FROM Laptop;
the following error message will be obtained:

Implicit conversion from data type varchar to money is not allowed. Use the CONVERT function to run this query.

This message implies that the system cannot accomplish implicit conversion of the character constant "Average price = " to data type money of the price column. In such cases, explicit type conversion can help. In so doing, as the above message says, you can use the CONVERT function. However this function is not based on the SQL-92 standard. Because of this, it is preferable to use the standard function CAST. So, we start with CAST.

If we rewrite above query as follows

Console
Execute
  1. SELECT 'Average price = ' + CAST(AVG(price) AS CHAR(15))
  2. FROM Laptop;

we get desired result:

  1. Average price = 1003.33

We have used the expression for explicit type conversion CAST to bring the average value to the character view. The CAST statement has very simple syntax:

  1. CAST(< expression > AS < DATA type >)

Attention:

Firstly, it should be noted that some data type conversions are not supported. (SQL-92 Standard involves the table of allowed conversions). Secondly, NULL value is converted into NULL value also.

Next page
Pages 1 2 3
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.