Conversion Functions

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.