Skip to main content

Cast / Conversion functions and operators

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

Some more 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);

Casting operators for character string literals

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'