SQL Reference ยท Operators and Predicates
SQL CAST function
Reference for SQL CAST(expr AS type): convert between text, integers, decimals and dates, how SQLite casts leniently, failure behavior and dialect shortcuts.
CAST(expression AS type) converts a value to another data type. It is standard SQL and the portable way to turn text into numbers, numbers into text, or decimals into integers. Use it when an operation needs a particular type: dividing two integers as decimals, sorting numbers stored as text, or joining on columns of different types.
Databases differ in how strict a cast is. PostgreSQL and SQL Server raise an error for CAST('abc' AS INTEGER); SQLite quietly returns 0.
SELECT CAST('42' AS INTEGER) + 1;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
CAST(expression AS data_type)
| Part | Description |
|---|---|
expression |
The value to convert. |
data_type |
The target type: INTEGER, DECIMAL(10, 2), VARCHAR(20), DATE, TEXT, ... Available types depend on the database. |
Returns: the converted value; CAST(NULL AS type) is NULL. A value that cannot be converted raises an error in most databases.
SQLite maps the target type name to one of five storage classes by its affinity rules: INTEGER, REAL, TEXT, BLOB or NUMERIC. Names such as VARCHAR(20) become TEXT and the length is ignored.
Dialect differences
| Behavior | SQLite | PostgreSQL | MySQL | SQL Server | Oracle |
|---|---|---|---|---|---|
CAST('abc' AS INTEGER) |
0 |
Error | 0 with a warning |
Error | Error |
CAST('12abc' AS INTEGER) |
12 |
Error | 12 with a warning |
Error | Error |
| Real to integer | Truncates toward zero | Rounds | Rounds | Truncates | Rounds |
| Shortcut syntax | None | value::type |
CONVERT(value, type) |
CONVERT(type, value) |
TO_NUMBER(), TO_CHAR(), TO_DATE() |
| Safe variant | None needed | None (use a regex check) | None | TRY_CAST() |
CAST(... DEFAULT x ON CONVERSION ERROR AS ...) (12.2+) |
CAST(... AS DATE) |
No date type: stays text | Yes | Yes | Yes | Yes |
-- PostgreSQL SELECT '42'::integer, 7.5::integer; -- 42, 8 -- SQL Server SELECT TRY_CAST('abc' AS INT); -- NULL instead of an error SELECT CONVERT(VARCHAR(10), GETDATE(), 23); -- Oracle SELECT TO_NUMBER('42') FROM dual;
Usage notes
- Cast the right side, not the column.
WHERE CAST(id_text AS INTEGER) = 5cannot use an index onid_text. Compareid_text = '5'or store the right type to begin with. See sargable predicates. - Integer division fix:
CAST(a AS REAL) / b(orDECIMAL) avoids truncating. See arithmetic operators. - Rounding differs. Casting
7.9to an integer truncates in SQLite and SQL Server, but rounds in PostgreSQL, MySQL and Oracle. CallROUND(),CEIL() and FLOOR()first when you care. - MySQL type names:
CASTaccepts only a few targets there (SIGNED,UNSIGNED,DECIMAL(p, s),CHAR,DATE,DATETIME, ...), so writeCAST(x AS SIGNED)instead ofAS INTEGER. - Text length:
CAST(x AS VARCHAR(5))truncates in some databases and errors in others. - Dates:
CAST(text AS DATE)expects the ISO formatYYYY-MM-DDin most engines. SQLite has no date type, so the result stays text; useDATE(). - Implicit conversion happens in comparisons and arithmetic, but rules differ. An explicit
CASTdocuments your intent and behaves predictably. - Check with
TYPEOF()in SQLite to see which storage class a value ended up in. - Booleans:
CAST('true' AS BOOLEAN)works in PostgreSQL but not in SQLite, which has no boolean type.