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 -
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
---- 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%'
|
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