Navigation
KSQL

Functions

KSQL provides a fixed set of built-in functions. User-defined functions are not supported.

For aggregate functions (COUNT, SUM, AVG, etc.), see Grouping & Aggregation.

String functions

Function Signature Description Example
UPPER UPPER(str) Convert to uppercase UPPER(Name)
LOWER LOWER(str) Convert to lowercase LOWER(Email)
LENGTH LENGTH(str) Number of characters LENGTH(Description)
TRIM TRIM(str) Remove leading/trailing whitespace TRIM(Name)
CONCAT CONCAT(a, b, ...) Join strings together CONCAT(First, ' ', Last)
SUBSTR SUBSTR(str, start, length) Extract substring (1-based) SUBSTR(Phone, 1, 3)
SUBSTRING SUBSTRING(str, start, length) Alias for SUBSTR SUBSTRING(Code, 2, 4)
REPLACE REPLACE(str, find, replacement) Replace all occurrences REPLACE(Phone, '-', '')
LEFT LEFT(str, n) First N characters LEFT(Name, 1)
RIGHT RIGHT(str, n) Last N characters RIGHT(Phone, 4) 
-- Normalize for case-insensitive search
SELECT * FROM Customers WHERE LOWER(Email) LIKE '%@example.com'

-- Extract area code
SELECT Name, LEFT(Phone, 3) AS area_code FROM Customers

-- Clean up data
SELECT TRIM(Name) AS name, REPLACE(Phone, '-', '') AS phone FROM Customers

-- First initial + last name
SELECT CONCAT(LEFT(FirstName, 1), '. ', LastName) AS short_name FROM Employees

You can also use || for concatenation: first_name || ' ' || last_name.

Math functions

Function Signature Description Example
ABS ABS(n) Absolute value ABS(Balance)
ROUND ROUND(n, decimals) Round to N decimal places ROUND(Price, 2)
CEIL CEIL(n) Round up to nearest integer CEIL(Rating)
FLOOR FLOOR(n) Round down to nearest integer FLOOR(Score)
POWER POWER(base, exp) Raise to a power POWER(Growth, 2)
MOD MOD(a, b) Remainder after division MOD(RowNum, 2) 
-- Round revenue to thousands
SELECT Name, ROUND(Revenue / 1000, 1) AS rev_k FROM Customers

-- Distance from a target
SELECT Name, ABS(Revenue - 50000) AS distance_from_target FROM Customers

-- Compound growth projection
SELECT Name, Revenue * POWER(1.05, Years) AS projected FROM Customers

Date functions

Date columns store Unix timestamps (seconds since epoch). These functions help you work with them in a readable way.

Function Signature Description Example
NOW NOW() Current Unix timestamp WHERE CreatedAt > NOW() - 86400
DATE DATE(string) Parse a date string to timestamp WHERE CreatedAt > DATE('2024-01-01')
DATE_FORMAT DATE_FORMAT(ts, format) Format timestamp as string DATE_FORMAT(CreatedAt, '%Y-%m-%d')
DATE_SUB DATE_SUB(ts, days) Subtract days from timestamp DATE_SUB(NOW(), 30)
DATE_ADD DATE_ADD(ts, days) Add days to timestamp DATE_ADD(DueDate, 7)
DATE_TRUNC DATE_TRUNC(unit, ts) Truncate to unit boundary DATE_TRUNC('month', CreatedAt)
LOOKBACK LOOKBACK(n, unit) Timestamp N units in the past WHERE CreatedAt > LOOKBACK(30, 'days')
YEAR YEAR(ts) Extract year (integer) YEAR(CreatedAt)
MONTH MONTH(ts) Extract month 1-12 (integer) MONTH(CreatedAt)
DAY DAY(ts) Extract day of month (integer) DAY(CreatedAt)
HOUR HOUR(ts) Extract hour 0-23 (integer) HOUR(CreatedAt)
MINUTE MINUTE(ts) Extract minute 0-59 (integer) MINUTE(CreatedAt)
SECOND SECOND(ts) Extract second 0-59 (integer) SECOND(CreatedAt)
DAYNAME DAYNAME(ts) Day of week name DAYNAME(CreatedAt) → 'Monday'
MONTHNAME MONTHNAME(ts) Month name MONTHNAME(CreatedAt) → 'January'
DATEDIFF DATEDIFF(ts1, ts2) Difference in days DATEDIFF(NOW(), CreatedAt)

DATE_FORMAT tokens

Token Description Example output
%Y 4-digit year 2024
%m 2-digit month 03
%d 2-digit day 15
%H 2-digit hour (24h) 14
%i 2-digit minute 05
%s 2-digit second 09

DATE_TRUNC units

Truncates a timestamp to the start of the given unit.

Unit Truncates to
'year' January 1 of that year
'month' First day of that month
'week' Start of that week (Sunday)
'day' Midnight of that day
'hour' Start of that hour

LOOKBACK units

Unit Aliases
'day' 'days'
'week' 'weeks'
'month' 'months'
'year' 'years'
'hour' 'hours'

Date examples

-- Last 30 days
SELECT * FROM Orders WHERE CreatedAt > LOOKBACK(30, 'days')

-- This month's activity
SELECT * FROM Events WHERE CreatedAt > DATE_TRUNC('month', NOW())

-- Format dates for display
SELECT Name, DATE_FORMAT(CreatedAt, '%Y-%m-%d') AS joined_date FROM Customers

-- Group by month
SELECT MONTH(CreatedAt) AS month, MONTHNAME(CreatedAt) AS month_name, COUNT(*) AS orders
FROM Orders
GROUP BY MONTH(CreatedAt), MONTHNAME(CreatedAt)
ORDER BY month

-- Age in days
SELECT Name, DATEDIFF(NOW(), CreatedAt) AS days_since_signup FROM Customers

-- Q1 2024
SELECT * FROM Orders
WHERE CreatedAt BETWEEN DATE('2024-01-01') AND DATE('2024-03-31')

-- Next week's deadlines
SELECT * FROM Tasks WHERE DueDate BETWEEN NOW() AND DATE_ADD(NOW(), 7)

-- Overdue items
SELECT Name, DATEDIFF(NOW(), DueDate) AS days_overdue
FROM Tasks
WHERE DueDate < NOW() AND Status != 'done'
ORDER BY days_overdue DESC

Date string comparisons are auto-converted: WHERE CreatedAt > '2024-06-01' is equivalent to WHERE CreatedAt > DATE('2024-06-01').

Conditional functions

Function Signature Description Example
IF IF(condition, then, else) Inline conditional IF(Revenue > 50000, 'High', 'Low')
IFNULL IFNULL(value, default) Default for null values IFNULL(Website, 'N/A')
NULLIF NULLIF(a, b) Returns NULL if a equals b NULLIF(Status, 'unknown')
COALESCE COALESCE(a, b, c, ...) First non-null value COALESCE(Phone, Email, 'No contact')
CAST CAST(expr AS type) Convert between types CAST(Revenue AS INTEGER)

CAST types

INTEGER, INT, REAL, FLOAT, DOUBLE, NUMERIC, TEXT, VARCHAR, CHAR, BOOLEAN, BOOL

-- Default for missing values
SELECT Name, IFNULL(Website, 'No website') AS website FROM Customers

-- Cascading fallback
SELECT Name, COALESCE(Phone, Email, Website, 'No contact') AS contact FROM Customers

-- Inline conditional
SELECT Name, IF(Revenue > 50000, 'Enterprise', 'SMB') AS segment FROM Customers

-- Type conversion
SELECT * FROM Products WHERE CAST(Price AS REAL) > 99.99

-- Treat empty strings as NULL
SELECT Name, NULLIF(Notes, '') AS notes FROM Customers

Next