📣

TiDB Cloud Premium is now in public preview. Unlimited growth, instant elasticity, advanced security for enterprise workloads. Try it out →

Get Started

Quick Start

Guides

Connect
Manage Resources
Data Integration
Load Data
Unload Data
Multimodal Data Analytics
Performance Tuning
Security & Reliability
Management

Troubleshooting

Tutorials

Ingest & Stream Data
Migrate Data

Reference

SQL Reference
General Reference

Support Services

Conversion Functions

This page provides a comprehensive overview of Conversion functions in TiDB Cloud Lake, organized by functionality for easy reference.

Type Conversion Functions

Function	Description	Example
CAST	Converts a value to a specified data type	`CAST('123' AS INT)` → `123`
TRY_CAST	Safely converts a value to a specified data type, returning NULL on failure	`TRY_CAST('abc' AS INT)` → `NULL`
TO_BOOLEAN	Converts a value to BOOLEAN type	`TO_BOOLEAN('true')` → `true`
TO_STRING	Converts a value to STRING type	`TO_STRING(123)` → `'123'`
TO_VARCHAR	Converts a value to VARCHAR type	`TO_VARCHAR(123)` → `'123'`
TO_TEXT	Converts a value to TEXT type	`TO_TEXT(123)` → `'123'`

Numeric Conversion Functions

Function	Description	Example
TO_INT8	Converts a value to INT8 type	`TO_INT8('123')` → `123`
TO_INT16	Converts a value to INT16 type	`TO_INT16('123')` → `123`
TO_INT32	Converts a value to INT32 type	`TO_INT32('123')` → `123`
TO_INT64	Converts a value to INT64 type	`TO_INT64('123')` → `123`
TO_UINT8	Converts a value to UINT8 type	`TO_UINT8('123')` → `123`
TO_UINT16	Converts a value to UINT16 type	`TO_UINT16('123')` → `123`
TO_UINT32	Converts a value to UINT32 type	`TO_UINT32('123')` → `123`
TO_UINT64	Converts a value to UINT64 type	`TO_UINT64('123')` → `123`
TO_FLOAT32	Converts a value to FLOAT32 type	`TO_FLOAT32('123.45')` → `123.45`
TO_FLOAT64	Converts a value to FLOAT64 type	`TO_FLOAT64('123.45')` → `123.45`

Binary and Specialized Conversion Functions

Function	Description	Example
TO_BINARY	Converts a value to BINARY type	`TO_BINARY('abc')` → `binary value`
TRY_TO_BINARY	Safely converts a value to BINARY type, returning NULL on failure	`TRY_TO_BINARY('abc')` → `binary value`
TO_HEX	Converts a value to hexadecimal string	`TO_HEX(255)` → `'FF'`
TO_VARIANT	Converts a value to VARIANT type	`TO_VARIANT('{"a": 1}')` → `{"a": 1}`
BUILD_BITMAP	Builds a bitmap from an array of integers	`BUILD_BITMAP([1,2,3])` → `bitmap value`
TO_BITMAP	Converts a value to BITMAP type	`TO_BITMAP([1,2,3])` → `bitmap value`

Please note the following when converting a value from one type to another:

When converting from floating-point, decimal numbers, or strings to integers or decimal numbers with fractional parts, TiDB Cloud Lake rounds the values to the nearest integer. This is determined by the setting numeric_cast_option (defaults to 'rounding') which controls the behavior of numeric casting operations. When numeric_cast_option is explicitly set to 'truncating', TiDB Cloud Lake will truncate the decimal part, discarding any fractional values.

SELECT CAST('0.6' AS DECIMAL(10, 0)), CAST(0.6 AS DECIMAL(10, 0)), CAST(1.5 AS INT);

┌──────────────────────────────────────────────────────────────────────────────────┐
│ cast('0.6' as decimal(10, 0)) │ cast(0.6 as decimal(10, 0)) │ cast(1.5 as int32) │
├───────────────────────────────┼─────────────────────────────┼────────────────────┤
│                             1 │                           1 │                  2 │
└──────────────────────────────────────────────────────────────────────────────────┘

SET numeric_cast_option = 'truncating';

SELECT CAST('0.6' AS DECIMAL(10, 0)), CAST(0.6 AS DECIMAL(10, 0)), CAST(1.5 AS INT);

┌──────────────────────────────────────────────────────────────────────────────────┐
│ cast('0.6' as decimal(10, 0)) │ cast(0.6 as decimal(10, 0)) │ cast(1.5 as int32) │
├───────────────────────────────┼─────────────────────────────┼────────────────────┤
│                             0 │                           0 │                  1 │
└──────────────────────────────────────────────────────────────────────────────────┘

The table below presents a summary of numeric casting operations, highlighting the casting possibilities between different source and target numeric data types. Please note that, it specifies the requirement for String to Integer casting, where the source string must contain an integer value.

Source Type	Target Type
String	Decimal
Float	Decimal
Decimal	Decimal
Float	Int
Decimal	Int
String (Int)	Int

TiDB Cloud Lake also offers a variety of functions for converting expressions into different date and time formats. For more information, see Date & Time Functions.

Conversion Functions

Type Conversion Functions

Numeric Conversion Functions

Binary and Specialized Conversion Functions

Was this page helpful?