📣

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

SQL Function Reference

TiDB Cloud Lake provides comprehensive SQL functions for all types of data processing. Functions are organized by importance and usage frequency.

Tip

Can't find the function you need? If none of the built-in functions below cover your logic, you can define your own with User-Defined Functions (UDFs). UDFs let you implement custom scalar, aggregate, and table functions using SQL expressions, Python, or JavaScript, then call them just like any built-in function. See Extending with User-Defined Functions below.

Core Data Functions

Category	Description
Numeric Functions	Mathematical operations and calculations
String Functions	Text manipulation and string processing
Date & Time Functions	Date, time, and temporal operations
Conversion Functions	Type casting and data format conversions
Conditional Functions	Logic and control flow operations

Analytics Functions

Category	Description
Aggregate Functions	Statistical calculations across multiple rows
Window Functions	Advanced analytics with window operations

Structured & Semi-Structured Data

Category	Description
Structured & Semi-Structured Functions	JSON, arrays, objects, and nested data processing

Search Functions

Category	Description
Full-Text Search Functions	Full-text search and relevance scoring

Vector Functions

Category	Description
Vector Functions	Vector similarity and distance calculations

Geospatial Functions

Category	Description
Geospatial Functions	Geometry, GeoHash, and H3 spatial operations

Data Management

Category	Description
Table Functions	File inspection, data generation, and system information
System Functions	System information and management operations
Context Functions	Current session, user, and database information

Security & Integrity

Category	Description
Hash Functions	Data hashing and integrity verification
Bitmap Functions	High-performance bitmap operations and analytics
UUID Functions	Universally unique identifier generation
IP Address Functions	Network address manipulation and validation

Utility Functions

Category	Description
Interval Functions	Time unit conversion and interval creation
Sequence Functions	Auto-incrementing sequence value generation
Data Anonymization Functions	Data masking and anonymization utilities
Test Functions	Testing and debugging utilities
Other Functions	Miscellaneous helpers and utilities

Extending with User-Defined Functions

When the built-in functions above don't cover your specific logic, define your own with User-Defined Functions (UDFs). Once created, a UDF is called exactly like a built-in function in your queries.

Function Type	Use It When
Scalar Function (SQL)	You want to reuse a SQL expression across queries (math, string formatting).
Scalar Function (Python/JavaScript)	Your logic needs control flow, external libraries, or advanced algorithms.
Aggregate Function	You need a custom aggregation that built-in aggregates can't express.
Table Function	You want a reusable, parameterized query that returns a result set.

For a full comparison of UDF types and syntax, see User-Defined Function.