Declaration
CAST(expr AS type)
Description
The CAST() function explicitly converts an expression of one data type to another, that is, it converts the value of the expr field to the type data type.
Parameters
The parameters are described as follows:
expr: any valid SQL expression.AS: Separates two parameters. The data beforeASis the data to be processed, and the data afterASis the data type to be converted.type: The data type supported by the target system. Valid values:CHAR[(N)] [CHARACTER SET charset_name]Generates a string of the
CHARdata type. Unless the expressionexpris empty (length 0), the result type isCHAR(0). If an optional lengthNis specified,CHAR(N)will enforce conversion to use no more thanNcharacters; values shorter thanNcharacters will not be padded. If the optional lengthNis not specified, OceanBase AI Database calculates the maximum length based on the expression. If there is noCHARACTER SET charset_nameclause,CHARgenerates a string with the default character set.DATE: Generates aDATEvalue.DATETIME [ (M) ]: Generates aDATETIMEvalue. TheMvalue is optional and specifies the precision of seconds in fractional form, with a range of [0,6].DECIMAL [ (M [,D] ) ]: Generates aDECIMALvalue. TheMandDvalues are optional and specify the maximum number of digits (precision) and the number of decimal places, respectively. The maximum value forMis 65, and the maximum value forDis 30. IfDis omitted, it defaults to0. IfMis omitted, it defaults to10.SIGNED [INTEGER]: Generates a signedBIGINTvalue.JSON: Generates aJSONvalue. For conversion rules between JSON and other types, see JSON data type conversion.TIME [ (M) ]: Generates aTIMEvalue. If the optional M value is provided, it specifies the precision of seconds in the fractional part.UNSIGNED [INTEGER]: Generates an unsignedBIGINTvalue.DECIMAL(m,d): a fixed-point number type, wheremindicates the total number of digits anddindicates the number of decimal places.FLOAT: single-precision floating-point number, suitable for approximate values.BIGINT: Explicitly converts the value of expressionexprto theBIGINTtype.INTorINTEGER: a 32-bit signed integer.SMALLINT: a 16-bit signed integer.TINYINT: an 8-bit signed integer.ARRAY: Converts the elements of aJSONarray into an array of a specific type. Supports declaring array element types in theARRAY<element_type>format, and also supports nested arrays, such asARRAY<ARRAY<INT>>, which can be used to represent multidimensional array structures.
Usage instructions
The CAST function supports the following data type conversions:
The data types of the two expressions are exactly the same.
The two expressions are implicitly convertible.
Data types must be explicitly converted.
When the value of expression expr exceeds the numeric range of the target data type, the type conversion returns
null.Converting a high-precision data type to a low-precision data type will result in precision loss.
Converting fixed-point numbers (
DECIMAL(m,d)) or floating-point numbers (DOUBLE,FLOAT) to integers (INT/INTEGER,SMALLINT, orTINYINT) will result in precision loss.If the expression
expris of theVARCHARtype and not a numeric value, and is converted to theINT/INTEGER,SMALLINT, orTINYINTtype, the result is0.If the expression
expris of the JSON type and not a numeric value, converting it to theINT/INTEGER/SMALLINT/TINYINTtype will return an error.If the
VARCHARdata orJSONdata does not conform to theARRAYformat, an error will be reported during conversion.
When a user attempts an impossible conversion, OceanBase AI Database displays an error message. If the length of a data type is not specified during conversion, the maximum internal length of OceanBase AI Database is used. For example, VARCHAR is 262,143 bytes, and NUMBER is 65-bit floating-point precision.
The CAST() operator supports operations with signed and unsigned 64-bit values. If you use a numeric operator (such as +) and one of the operands is an unsigned integer, the result is unsigned. You can explicitly declare the result as a signed or unsigned 64-bit integer by using the SIGNED or UNSIGNED keyword. If either operand is a floating-point value, the result is a floating-point value.
Examples
Convert the numeric value 0 to the
DATEtype.obclient> SELECT CAST(0 AS DATE); +-----------------+ | CAST(0 AS DATE) | +-----------------+ | 0000-00-00 | +-----------------+ 1 row in setConvert the value 123 to the
TIMEtype.obclient> SELECT CAST(123 AS TIME); +-------------------+ | CAST(123 AS TIME) | +-------------------+ | 00:01:23 | +-------------------+ 1 row in setConvert the value 123 to the
DATETIMEtype.obclient> SELECT CAST(123 AS DATETIME(4)); +--------------------------+ | CAST(123 AS DATETIME(4)) | +--------------------------+ | 2000-01-23 00:00:00.0000 | +--------------------------+ 1 row in setConvert the value 123 to the
DECIMALtype.obclient> SELECT CAST(123 AS DECIMAL(3,2)); +---------------------------+ | CAST(123 AS DECIMAL(3,2)) | +---------------------------+ | 9.99 | +---------------------------+ 1 row in setConvert the string "123" to the JSON type.
obclient> SELECT CAST("123" AS JSON); +---------------------+ | CAST("123" AS JSON) | +---------------------+ | 123 | +---------------------+ 1 row in setThe results of "1-2" are represented using unsigned and signed formats, respectively.
obclient> SELECT CAST(1-2 AS UNSIGNED), CAST(cast(1-2 AS UNSIGNED) AS SIGNED); +-----------------------+---------------------------------------+ | CAST(1-2 AS UNSIGNED) | CAST(cast(1-2 AS UNSIGNED) AS SIGNED) | +-----------------------+---------------------------------------+ | 18446744073709551615 | -1 | +-----------------------+---------------------------------------+ 1 row in setPerform numeric operations using
CAST().obclient> SELECT CAST(1 AS UNSIGNED) - 2.0; +---------------------------+ | CAST(1 AS UNSIGNED) - 2.0 | +---------------------------+ | -1.0 | +---------------------------+ 1 row in setConvert the value 123 to the
CHARtype.obclient> SELECT CAST(123 AS CHAR(2)); +----------------------+ | CAST(123 AS CHAR(2)) | +----------------------+ | 12 | +----------------------+ 1 row in setConvert the value 1 to the
CHARtype and specify the character set.obclient> SELECT CAST(1 AS CHAR CHARACTER SET utf8mb4); +---------------------------------------+ | CAST(1 AS CHAR CHARACTER SET utf8mb4) | +---------------------------------------+ | 1 | +---------------------------------------+ 1 row in setConverts the character type
123to an integer type, and converts the integer1to anINTtype.obclient> SELECT CAST('123' AS INT),CAST(1 AS CHAR(10)); +--------------------+---------------------+ | CAST('123' AS INT) | CAST(1 AS INT) | +--------------------+---------------------+ | 123 | 1 | +--------------------+---------------------+ 1 row in set (0.035 secConvert the
JSONdata [1,2,3] toARRAYdata, where theARRAYdata consists ofINTtype values. The statement is as follows:obclient> SELECT CAST('[1,2,3]' AS ARRAY<INT>); +-------------------------------------+ | CAST('[1,2,3]' AS ARRAY<INT> | +-------------------------------------+ | [1,2,3] | +-------------------------------------+ 1 row in setConvert the nested array of type
JSON[[1,2],[3,4]]to nestedARRAY<ARRAY<INT>>data. The statement is as follows:obclient> SELECT CAST('[[1,2],[3,4]]' AS ARRAY<ARRAY<INT>>); +--------------------------------------------+ | CAST('[[1,2],[3,4]]' AS ARRAY<ARRAY<INT>>) | +--------------------------------------------+ | [[1,2],[3,4]] | +--------------------------------------------+ 1 row in setConvert the floating-point number
123.456to theBIGINTtype.obclient> SELECT CAST(123.456 AS BIGINT);The return result is as follows:
+-------------------------+ | CAST(123.456 AS BIGINT) | +-------------------------+ | 123 | +-------------------------+ 1 row in set