Wednesday, June 8, 2016

SQL - CAST function

In SQL Server, we require explicit conversions for one data type value to another data type and these conversions are those conversions that require the CAST or CONVERT functions to be specified. 
CAST function is known as ANSI SQL-92 compliant (standard) function which is introduced with SQL Server 2005 release. It is used to convert an expression of one data type to another but no need to format the results. Due to having ANSI-SQL nature, it is more portable across different database platforms.

Whenever we call the CAST function, it requires the data type that the initial value should be cast as -
Syntax
CAST (Expression AS Data_Type [<Optional length>]
Arguments
Expression: It could be any valid SQL expression or variable.
Data_Type: It must Be a valid data type and will be change based on the database provider that we use.
Optional length: It is used for nchar, char, varchar, nvarchar, varbinary and binary data type.
Return Types
Returns expression translated to data_type.

CAST is required to preserve precision when converting between Decimal and Numeric types. Here are some examples, where we can understand CAST Function as following-
  • If you want to convert Decimal value into Int data type then it will return the truncated number instead round it.
  • If you try to convert Decimal to money data type then it would return the same number as the decimal data type has.
  • If you try to convert Decimal to varchar data type with defined length then it would return the same number as the decimal data type has.
  • You can use the cast function within the where clause also and use into the concatenate two string also.


----- Local Table Variable
DECLARE @FruitMaster Table
(FruitName Varchar(20), Price Decimal(10,2))

----- Insert data into table variable
INSERT INTO @FruitMaster
Values('Mango',12.34), ('Orange',35.73),('Apple',109.80),
('Grapes',68.98), ('Sapodilla',102.75)

---- Pull data
SELECT * from @FruitMaster
FruitName
Price
Mango
12.34
Orange
35.73
Apple
109.80
Grapes
68.98
Sapodilla
102.75

---- Pull data with Cast conversation
SELECT FruitName, Price,

---- Cast decimal into int
PriceInt= CAST(Price as INT),

---- Cast decimal into money
PriceMoney= CAST(Price as Money),

---- CAST to concatenate
Details= CONCAT(FruitName, CAST(Price as Varchar(6))),

---- CAST with datetime data
RunDate=Cast(getdate() as date)

FROM @FruitMaster

---- Cast decimal into Varchar with the LIKE clause
WHERE CAST(Price as Varchar(10)) like '1%'
FruitName
Price
PriceInt
PriceMoney
Details
RunDate
Mango
12.34
12
12.34
Mango12.34
6/8/2016
Apple
109.80
109
109.8
Apple109.80
6/8/2016
Sapodilla
102.75
102
102.75
Sapodilla102.75
6/8/2016


In the above example, we can see that how cast function behaves with different data type conversation. If you want to apply the style formatting with the date data type then it is not the right choice for the data type conversation.

Conclusion
As we understand that CAST function is very feasible across database platforms due to having the ANSI-SQL standards. CAST will truncate the value or variable or column if the character size is smaller than the size required for full display.

No comments:

Post a Comment