SyntaxShelf

Type conversion

2 snippets in SQL

SQLSQL

CAST / Type Conversion

SQL · String Functions
Syntax
CAST(expression AS data_type)
expression::data_type  -- PostgreSQL shorthand
Example
SELECT
  CAST(price AS INTEGER) AS rounded_price,
  CAST(order_date AS VARCHAR) AS date_string,
  '42'::INTEGER + 8 AS sum_pg  -- PostgreSQL only
FROM products;
Output
-- rounded_price | date_string | sum_pg
-- 29             | 2025-03-15  | 50

Note CAST is ANSI standard. PostgreSQL's :: shorthand is shorter but not portable. Be careful casting - CAST('abc' AS INTEGER) will throw an error. Use TRY_CAST in SQL Server for safe conversions.

Implicit Type Conversion

SQL · Common Mistakes
Syntax
-- WRONG: comparing string column to integer
WHERE phone_number = 2065551234
-- The DB converts every phone_number to int for comparison!

-- CORRECT: compare with matching types
WHERE phone_number = '2065551234'
Example
-- Slow: index on user_code (VARCHAR) is not used
SELECT * FROM users WHERE user_code = 12345;

-- Fast: index is used
SELECT * FROM users WHERE user_code = '12345';
Output
-- Type mismatch forces a full table scan because the index cannot be used

Note When you compare a string column to a number, the database may cast every row's string value to a number, which prevents index usage and can cause errors on non-numeric strings. Always match the data type in your comparison.

Frequently asked questions

How does SQL handle type conversion?
SQL covers this with 2 copy-ready snippets on this page. The "CAST / Type Conversion" snippet in SQL uses `CAST(expression AS data_type)`.
Which statement does the SQL example use?
The "CAST / Type Conversion" snippet uses `CAST(expression AS data_type)`, from the String Functions section of the SQL cheat sheet.
What other SQL snippets are shown for "type conversion"?
Besides "CAST / Type Conversion", this page also shows "Implicit Type Conversion".
Is there anything to watch out for?
Yes. For "CAST / Type Conversion": CAST is ANSI standard. PostgreSQL's :: shorthand is shorter but not portable. Be careful casting - CAST('abc' AS INTEGER) will throw an error. Use TRY_CAST in SQL Server for safe conversions.

Browse all tasks by intent