Casting and conversion functions are available for all built-in system data types.
| Function | Return type | Description | Example query | Result |
|---|---|---|---|---|
CAST(value AS data_type) | Same as data_type | Converts the data type of value to the type specified by data_type. | SELECT CAST(123 AS VARCHAR(10)); | "123" |
CONVERT(value, data_type) | Same as data_type | Converts the data type of value to the type specified by data_type. | SELECT CONVERT(123, DECIMAL(10,3)); | 123.000 |
| Operator | Description | Example |
|---|---|---|
e'str_value' | Treats every instance of a backslash (\) character in a string as an escape character, thus activating escape sequences such as \f, \t, \n, \b, \u, etc. | SELECT e'A\fB\tC\n\\Z'; |
r'str_value' | Treats every instance of a backslash (\) character in a string as a raw string character (i.e. there is no conversion of any present escape sequence). | SELECT r'A\fB\tC\n\\Z'; |
x'hexadecimals' | Converts hexadecimals in string format to blob (i.e. binary large object) data type. | SELECT x'abcd'; |
BLOB 'hexadecimals' | Converts hexadecimals in string format to blob (i.e. binary large object) data type. | SELECT BLOB 'abcd'; |
inet 'str_value' | Converts string value to inet data type. | SELECT inet '192.168.1.5/24'; |
json 'str_value' | Converts string value to json data type. | SELECT json '{"a":[1,2,4]}'; |
url 'str_value' | Converts string value to url data type. | SELECT url 'https\://www.monetdb.org/Home'; |
uuid 'str_value' | Converts string value to uuid data type. | SELECT uuid 'e31960fb-dc8b-452d-ab30-b342723e756a'; |
Casting operators for temporal data types (i.e. Date & Time) can be found here.
SELECT CAST(TRUE AS SMALLINT);
SELECT CAST(42 AS INT);
SELECT CAST(123.45 AS REAL);
SELECT CAST('123.45' AS DOUBLE PRECISION);
SELECT CAST(23.45 AS DECIMAL(5,2)); -- Precision of 5 digits, of which 2 decimal digits
SELECT CAST('2020-07-29' AS DATE);
SELECT CAST('17:44:59' AS TIME);
SELECT CAST('17:44:59.123456' AS TIME);
SELECT CAST('2020-07-29 17:44:59' AS TIMESTAMP);
SELECT CAST('2020-07-29T17:44:59' AS TIMESTAMP);
SELECT CAST('2020-07-29 17:44:59.123456' AS TIMESTAMP);
SELECT CAST('17:44:59.321+01:30' AS TIMETZ);
SELECT CAST('2020-07-29 17:44:59.321+01:30' AS TIMESTAMPTZ);
SELECT CAST('67' AS INTERVAL MONTH);
SELECT CAST('120' AS INTERVAL DAY);
SELECT CAST('86400.123' AS INTERVAL SECOND);
SELECT CAST('18:40:05' AS INTERVAL HOUR TO SECOND);
SELECT CAST('2 18:40:05.123' AS INTERVAL DAY TO SECOND(3));
SELECT CAST('2-5' AS INTERVAL YEAR TO MONTH);
SELECT CAST('a4cd' AS BLOB); -- Use 2 hex digits per byte, so 'a4cd' is 2 bytes long.
SELECT CAST('abcde' AS CLOB);
SELECT CAST('192.168.1.5/24' AS INET);
SELECT CAST(r'{"a":[1,2,4]}' AS JSON);
SELECT CAST('https://www.monetdb.org/Home' AS URL);
SELECT CAST('e31960fb-dc8b-452d-ab30-b342723e756a' AS UUID);
-- Or using convert instead of cast:
SELECT CONVERT('a4cd', BLOB);
SELECT CONVERT('abcde', CLOB);
SELECT CONVERT('192.168.1.5/24', INET);
SELECT CONVERT(r'{"a":[1,2,4]}', JSON);
SELECT CONVERT('https://www.monetdb.org/Home', URL);
SELECT CONVERT('e31960fb-dc8b-452d-ab30-b342723e756a', UUID);