The CAST function in SQL is used to convert a value from one data type to another. The syntax for using the CAST function is as follows:
CAST(expression AS target_data_type)
Parameters:
- expression: This is the value or column that you want to convert.
- target_data_type: This is the data type to which you want to convert the expression (e.g., INTEGER, VARCHAR, DATE, etc.).
Example Usages:
1. Converting a string to an integer:
SELECT CAST('123' AS INT) AS ConvertedValue;
This would return 123 as an integer.
2. Converting an integer to a string:
SELECT CAST(123 AS VARCHAR(10)) AS ConvertedValue;
This would return '123' as a string.
3. Converting a string to a date:
SELECT CAST('2023-10-15' AS DATE) AS ConvertedValue;
This would return the date 2023-10-15.
4. Using CAST in a table query:
SELECT employee_id,
CAST(salary AS DECIMAL(10, 2)) AS formatted_salary
FROM employees;
This example converts a salary field to a decimal type with two decimal places.
Important Notes:
- Make sure the conversion is valid; for example, trying to convert a non-numeric string to an INTEGER will result in an error.
- The specific data types available for conversion may vary depending on the SQL database system you are using (e.g., MySQL, SQL Server, Oracle, PostgreSQL).
Using CAST can help ensure that your data types are appropriate for calculations or comparisons in your queries.