SQL conversion functions are used to convert data from one type to another. Examples include CAST (explicitly converts data from one type to another), CONVERT (converts data types to another data type). These functions facilitate data transformation and ensure compatibility between different data types within SQL queries.
CAST / CONVERT: The SQL CAST and CONVERT functions are
used to explicitly convert data from one data type to another.
Consider a table named Products
with a column Price stored as a
VARCHAR. We want to convert the price to a numeric data type (DECIMAL).
SELECT CAST(Price AS DECIMAL(10,2)) AS ConvertedPrice FROM Products;
In this example, the CAST function converts the Price column from VARCHAR to
DECIMAL with a precision of 10 and a scale of 2. Alternatively, you can use the
CONVERT function:
SELECT CONVERT(DECIMAL(10,2),
Price) AS ConvertedPrice FROM Products;
Both queries will produce the same result, converting the Price column to a numeric
data type (DECIMAL) with two decimal places.
COALESCE: The SQL COALESCE function is used to return
the first non-null expression among its arguments.
Consider a table named Employees
with columns EmployeeID, FirstName, MiddleName, and LastName. We want to retrieve
the full name of each employee, considering that some employees might not have
a middle name.
SELECT
EmployeeID, COALESCE(FirstName + ' ', '') + COALESCE(MiddleName + ' ', '') +
LastName AS FullName FROM Employees;
In this example, the COALESCE function is used to
concatenate the FirstName,
MiddleName, and LastName columns, ensuring
that if any of these columns are null, they are treated as empty strings. This
prevents null values from disrupting the concatenation process. The result set
will contain the EmployeeID
along with the calculated FullName
for each employee.
Comments
Post a Comment