Casting and conversion functions are available for all built-in system data types.
Casting functions
| Function | Return type | Description | Example | Result |
|---|---|---|---|---|
| CAST(col_expr AS data type) | same as requested data type | converts the value of the col_expr into the specified data type | cast(123 as varchar(10)) | '123' |
| CONVERT(col_expr , data type) | same as requested data type | converts the value of the col_expr into the specified data type | convert(123, decimal(10,3)) | 123.000 |
Examples
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('1234' as interval month);
select cast('86400.123' as interval second);
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);
| Operator | Description | Example |
|---|---|---|
| x'hexadecimals' | convert string value (pairs of hexadecimals) to binary string (blob) | x'abcd' |
| blob 'hexadecimals' | convert string value (pairs of hexadecimals) to blob type | blob 'abcd' |
| inet 'valid inet address string' | convert string value to inet type | inet '192.168.1.5/24' |
| json 'valid json string' | convert string value to json type | json '{"a":[1,2,4]}' |
| url 'valid url string' | convert string value to url type | url 'https://www.monetdb.org/Home' |
| uuid 'valid uuid string' | convert string value to uuid type | uuid 'e31960fb-dc8b-452d-ab30-b342723e756a' |
| E'string with backslash escapes' | convert backslash escapes (such as: \f \t \n \b \u etc) in string as specific (control) characters | e'A\fB\tC\n\Z' |
| R'any string' | NO conversion of backslash escapes is done. Treat string characters as provided, so raw | r'A\fB\tC\n\Z' |