Sql cast – examples

Sql cast: in SQL language implementations the implicit type conversion can be performed. For example, in SQL Server and Sybase ASE. Transact-SQL when comparing or combining values of smallint and int types, data of smallint type are implicitly converted to int type.

You will now learn how to use the CAST function in SQL Server (Transact-SQL) with syntax and examples.

Description of the Sql cast function

In SQL Server (Transact-SQL), the CAST function converts an expression from one data type to another data type.

Syntax of the Sql cast function

Syntax of the CAST function in SQL Server (Transact-SQL):

CAST( expression AS type [ (length) ] )

Parameters or arguments

• expression– value for conversion to another data type.
• type – data type to which you want to convert the expression. It can be one of the following: bigint, int, smallint, tinyint, bit, decimal, numeric, money, smallmoney, float, real, datetime, smalldatetime, char, varchar, text, nchar, nvarchar, ntext, binary, varbinary, or image.
• length – is optional. The length of the resulting data type for char, varchar, nchar, nvarchar, binary, and varbinary.

Note:

• When converting from float or numeric to integer, the CAST function truncates the result. For other conversions the CAST function will round up the result.
• See also CONVERT function.

Application
The CAST function can be used in future versions of SQL Server (Transact-SQL):
SQL Server vNext, SQL Server 2016, SQL Server 2015, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005

Example 1:
Let’s look at some examples of the SQL Server function of CAST to understand how to use the CAST function in SQL Server (Transact-SQL). For example:

SELECT CAST(12.81 AS int);
--Result: 12 (Result truncated)

SELECT CAST(12.81 AS float);
--Result: 12.81 (Result not truncated)

SELECT CAST(14.6 AS varchar);
--Result: '14.6'

SELECT CAST(14.6 AS varchar(4));
--Output: '14.6' SELECT CAST(14.6 AS varchar(4))

SELECT CAST('14.6' AS float); -Output: '14.6' SELECT CAST(14.6' AS varchar(4));
--Output: '14.6' SELECT CAST('14.6' AS float)

SELECT CAST('25.12.2017' AS datetime);
--Result: '2017-12-25 00:00:00:00.000'

Example 2:
Print the average price of laptops with the preceding text “average price = “.
Attempt to execute the request

SELECT 'Average price = '+AVG(price)
FROM Laptop;

will lead to an error message:

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

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

This message means that the system cannot perform an implicit conversion of varchar to money type. In such situations, explicit type conversion may help. As indicated in the error message, you can use the CONVERT function. However, this function is not standardized, so for portability purposes it is recommended to use the CAST standard expression. That’s where we’ll start.

So, if you rewrite our query in the form of

SELECT 'Average price = '+ CAST(AVG(price) AS CHAR(15))
FROM Laptop;,

we’ll get what we need:

Average price = 1003.33

We used an explicit CAST type conversion expression to convert the average price value to a string representation.

The syntax of CAST expression is very simple.

CAST(<expression> AS <data type>)

Attention:
Firstly, you should keep in mind that not all type conversions are possible (the standard contains a table of acceptable data type conversions). Secondly, the result of the CAST function for the value of the expression equal to NULL will also be NULL.

Let’s look at another example.

Example 3:
Determine the average year that ships are launched from the Ships table.
Request:

SELECT AVG(launched)
FROM Ships;

will result in 1926. Basically, everything is right, because we got what we asked for as a result – a year. However, the arithmetic mean will be about 1926,2381.

Here we should remind you that the aggregate functions (except COUNT, which always returns an integer) inherits the data type of the processed values. Since the field launched is an integer, we have received the average value with the discarded fractional part (note – not rounded).

And if we are interested in the result with a given accuracy, say, to two decimal places? Applying the CAST expression to the mean will not give us anything for the above reason. Indeed,

SELECT CAST(AVG(launched) AS NUMERIC(6,2))
FROM Ships;

will return to 1926.00. Consequently, CAST must be applied to an aggregate function argument:

SELECT AVG(CAST(launched AS NUMERIC(6,2)))
FROM Ships;

Result – 1926.90909. Wrong again. The reason is that when calculating the average value, an implicit type conversion was performed. Let’s take one more step:

SELECT CAST(AVG(CAST(launched AS NUMERIC(6,2))) AS NUMERIC(6,2))
FROM Ships;

The result is what we need – 1926.91. However, this decision looks very cumbersome. We’ll make an implicit type conversion work for us:

SELECT CAST(AVG(launched*1.0) AS NUMERIC(6,2))
FROM Ships;

Now we used an implicit conversion of an integer argument to an exact numeric type (EXACT NUMERIC) multiplying it by a real unit and then we applied an explicit type conversion of the result of an aggregate function.

Sql Training Online – Cast Function