SQL Commands

For security and stability reasons, we only allow a carefully selected collection of SQL functions. This ensures that queries cannot modify data, access system resources, or interfere with other users' data. The functions listed below have been tested and approved for use in the Polyteia platform.

If you need additional SQL functions for your use case, please contact us through our help and support page and we'll be happy to review your request.

Mathematical Functions

ABS - Absolute value

Returns the absolute (positive) value of a number.

Syntax: ABS(number)

Example:

SELECT ABS(-25) AS result
FROM '{{ds_your_dataset}}' 
LIMIT 10
-- Returns: 25

CEIL - Round up to nearest integer

Rounds a number up to the nearest integer.

Syntax: CEIL(number)

Example:

SELECT CEIL(4.3) AS result
FROM '{{ds_your_dataset}}' 
LIMIT 10
-- Returns: 5

FLOOR - Round down to nearest integer

Rounds a number down to the nearest integer.

Syntax: FLOOR(number)

Example:

SELECT FLOOR(4.8) AS result
FROM '{{ds_your_dataset}}' 
LIMIT 10
-- Returns: 4

ROUND - Round to nearest integer or decimal places

Rounds a number to the nearest integer or specified decimal places.

Syntax: ROUND(number, [decimal_places])

Example:

SELECT ROUND(4.567, 2) AS result
FROM '{{ds_your_dataset}}' 
LIMIT 10
-- Returns: 4.57

SQRT - Square root

Returns the square root of a number.

Syntax: SQRT(number)

Example:

SELECT SQRT(16) AS result
FROM '{{ds_your_dataset}}' 
LIMIT 10
-- Returns: 4

EXP - Exponential function

Returns e raised to the power of the given number.

Syntax: EXP(number)

Example:

SELECT EXP(1) AS result
FROM '{{ds_your_dataset}}' 
LIMIT 10
-- Returns: 2.718281828459045

LN - Natural logarithm

Returns the natural logarithm of a number.

Syntax: LN(number)

Example:

SELECT LN(2.718281828459045) AS result
FROM '{{ds_your_dataset}}' 
LIMIT 10
-- Returns: 1

LOG - Logarithm

Returns the logarithm of a number to a specified base.

Syntax: LOG(base, number)

Example:

SELECT LOG(10, 100) AS result
FROM '{{ds_your_dataset}}' 
LIMIT 10
-- Returns: 2

POWER - Raise to power

Returns a number raised to the power of another number.

Syntax: POWER(base, exponent)

Example:

SELECT POWER(2, 3) AS result
FROM '{{ds_your_dataset}}' 
LIMIT 10
-- Returns: 8

MOD - Modulo operation

Returns the remainder of a division operation.

Syntax: MOD(dividend, divisor)

Example:

SELECT MOD(10, 3) AS result
FROM '{{ds_your_dataset}}' 
LIMIT 10
-- Returns: 1

Aggregate Functions

COUNT - Count rows

Counts the number of rows or non-null values.

Syntax: COUNT(*) or COUNT(column)

Example:

SELECT COUNT(*) AS total_rows
FROM '{{ds_your_dataset}}' 
LIMIT 10

COUNT_DISTINCT - Count unique values

Counts the number of distinct (unique) values in a column.

Syntax: COUNT_DISTINCT(column)

Example:

SELECT COUNT_DISTINCT(department) AS unique_departments
FROM '{{ds_your_dataset}}' 
LIMIT 10

COUNT_IF - Conditional count

Counts rows that meet a specific condition.

Syntax: COUNT_IF(condition)

Example:

SELECT COUNT_IF(age > 30) AS adults_over_30
FROM '{{ds_your_dataset}}' 
LIMIT 10

SUM - Sum values

Calculates the sum of numeric values.

Syntax: SUM(column)

Example:

SELECT SUM(salary) AS total_salary
FROM '{{ds_your_dataset}}' 
LIMIT 10

AVG - Average value

Calculates the average of numeric values.

Syntax: AVG(column)

Example:

SELECT AVG(age) AS average_age
FROM '{{ds_your_dataset}}' 
LIMIT 10

MIN - Minimum value

Returns the smallest value in a column.

Syntax: MIN(column)

Example:

SELECT MIN(salary) AS lowest_salary
FROM '{{ds_your_dataset}}' 
LIMIT 10

MAX - Maximum value

Returns the largest value in a column.

Syntax: MAX(column)

Example:

SELECT MAX(salary) AS highest_salary
FROM '{{ds_your_dataset}}' 
LIMIT 10

STRING_AGG - Concatenate strings

Concatenates string values with a specified separator.

Syntax: STRING_AGG(column, separator)

Example:

SELECT STRING_AGG(name, ', ') AS all_names
FROM '{{ds_your_dataset}}' 
LIMIT 10
-- Returns: "John, Jane, Bob"

ANY_VALUE - Get any value

Returns any value from a group (useful with GROUP BY).

Syntax: ANY_VALUE(column)

Example:

SELECT department, ANY_VALUE(manager) AS sample_manager
FROM '{{ds_your_dataset}}' 
GROUP BY department
LIMIT 10

Type Conversion Functions

CAST - Convert data types

Converts a value from one data type to another.

Syntax: CAST(value AS data_type)

Example:

SELECT CAST('123' AS INTEGER) AS number
FROM '{{ds_your_dataset}}' 
LIMIT 10
-- Returns: 123

String Functions

SUBSTR - Extract substring from text

Extracts a portion of a string starting at a specific position.

Syntax: SUBSTR(string, start, length)

Example:

SELECT SUBSTR("Hello World", 1, 5) AS result
FROM '{{ds_your_dataset}}' 
LIMIT 10
-- Returns: "Hello"

LEFT - Get leftmost characters

Returns the leftmost characters from a string.

Syntax: LEFT(string, length)

Example:

SELECT LEFT("Hello World", 5) AS result
FROM '{{ds_your_dataset}}' 
LIMIT 10
-- Returns: "Hello"

RIGHT - Get rightmost characters

Returns the rightmost characters from a string.

Syntax: RIGHT(string, length)

Example:

SELECT RIGHT("Hello World", 5) AS result
FROM '{{ds_your_dataset}}' 
LIMIT 10
-- Returns: "World"

REPLACE - Replace text in string

Replaces all occurrences of a substring with another substring.

Syntax: REPLACE(string, old_text, new_text)

Example:

SELECT REPLACE("Hello World", "World", "DuckDB") AS result
FROM '{{ds_your_dataset}}' 
LIMIT 10
-- Returns: "Hello DuckDB"

REGEXP_REPLACE - Replace using regular expressions

Replaces text using regular expression patterns for advanced text manipulation.

Syntax: REGEXP_REPLACE(string, pattern, replacement)

Example:

-- Convert DD.MM.YYYY to YYYY-MM-DD format
SELECT REGEXP_REPLACE("19.02.2025", '(\d{2})\.(\d{2})\.(\d{4})', '\3-\2-\1') AS result
FROM '{{ds_your_dataset}}' 
LIMIT 10
-- Returns: "2025-02-19"

SUBSTRING - Alternative to SUBSTR

Extracts a portion of a string starting at a specific position (identical to SUBSTR).

Syntax: SUBSTRING(string, start, length)

Example:

SELECT SUBSTRING("Hello World", 1, 5) AS result
FROM '{{ds_your_dataset}}' 
LIMIT 10
-- Returns: "Hello"

TRIM - Remove whitespace

Removes whitespace from the beginning and end of a string.

Syntax: TRIM(string) or TRIM(characters FROM string)

Example:

SELECT TRIM("  Hello World  ") AS result
FROM '{{ds_your_dataset}}' 
LIMIT 10
-- Returns: "Hello World"

RTRIM - Remove whitespace from right

Removes whitespace from the end (right side) of a string.

Syntax: RTRIM(string)

Example:

SELECT RTRIM("Hello World   ") AS result
FROM '{{ds_your_dataset}}' 
LIMIT 10
-- Returns: "Hello World"

LTRIM - Remove whitespace from left

Removes whitespace from the beginning (left side) of a string.

Syntax: LTRIM(string)

Example:

SELECT LTRIM("   Hello World") AS result
FROM '{{ds_your_dataset}}' 
LIMIT 10
-- Returns: "Hello World"

UPPER - Convert to uppercase

Converts all letters in a string to uppercase.

Syntax: UPPER(string)

Example:

SELECT UPPER("Hello World") AS result
FROM '{{ds_your_dataset}}' 
LIMIT 10
-- Returns: "HELLO WORLD"

LOWER - Convert to lowercase

Converts all letters in a string to lowercase.

Syntax: LOWER(string)

Example:

SELECT LOWER("HELLO WORLD") AS result
FROM '{{ds_your_dataset}}' 
LIMIT 10
-- Returns: "hello world"

CHAR_LENGTH - Get character count

Returns the number of characters in a string.

Syntax: CHAR_LENGTH(string)

Example:

SELECT CHAR_LENGTH("Hello") AS result
FROM '{{ds_your_dataset}}' 
LIMIT 10
-- Returns: 5

LENGTH - Get string length

Returns the length of a string (identical to CHAR_LENGTH).

Syntax: LENGTH(string)

Example:

SELECT LENGTH("Hello World") AS result
FROM '{{ds_your_dataset}}' 
LIMIT 10
-- Returns: 11

CONCAT - Concatenate strings

Concatenates multiple strings into a single string.

Syntax: CONCAT(string1, string2, ...)

Example:

SELECT CONCAT("Hello", " ", "World") AS result
FROM '{{ds_your_dataset}}' 
LIMIT 10
-- Returns: "Hello World"

REVERSE - Reverse string

Reverses the order of characters in a string.

Syntax: REVERSE(string)

Example:

SELECT REVERSE("Hello") AS result
FROM '{{ds_your_dataset}}' 
LIMIT 10
-- Returns: "olleH"

REPEAT - Repeat string

Repeats a string a specified number of times.

Syntax: REPEAT(string, count)

Example:

SELECT REPEAT("Ha", 3) AS result
FROM '{{ds_your_dataset}}' 
LIMIT 10
-- Returns: "HaHaHa"

LPAD - Left pad string

Pads a string on the left with characters to a specified length.

Syntax: LPAD(string, length, pad_character)

Example:

SELECT LPAD("42", 5, "0") AS result
FROM '{{ds_your_dataset}}' 
LIMIT 10
-- Returns: "00042"

RPAD - Right pad string

Pads a string on the right with characters to a specified length.

Syntax: RPAD(string, length, pad_character)

Example:

SELECT RPAD("Test", 8, "-") AS result
FROM '{{ds_your_dataset}}' 
LIMIT 10
-- Returns: "Test----"

STARTS_WITH - Check if string starts with text

Checks if a string starts with a specific text.

Syntax: STARTS_WITH(string, prefix)

Example:

SELECT STARTS_WITH("Hello World", "Hello") AS result
FROM '{{ds_your_dataset}}' 
LIMIT 10
-- Returns: true

ENDS_WITH - Check if string ends with text

Checks if a string ends with a specific text.

Syntax: ENDS_WITH(string, suffix)

Example:

SELECT ENDS_WITH("Hello World", "World") AS result
FROM '{{ds_your_dataset}}' 
LIMIT 10
-- Returns: true

POSITION - Find position of substring

Returns the position of the first occurrence of a substring.

Syntax: POSITION(substring IN string)

Example:

SELECT POSITION('World' IN 'Hello World') AS result
FROM '{{ds_your_dataset}}' 
LIMIT 10
-- Returns: 7

STRPOS - Alternative position function

Returns the position of the first occurrence of a substring (alternative syntax).

Syntax: STRPOS(string, substring)

Example:

SELECT STRPOS("Hello World", "World") AS result
FROM '{{ds_your_dataset}}' 
LIMIT 10
-- Returns: 7

INSTR - Find position of substring

Returns the position of the first occurrence of a substring (another alternative syntax).

Syntax: INSTR(string, substring)

Example:

SELECT INSTR("Hello World", "World") AS result
FROM '{{ds_your_dataset}}' 
LIMIT 10
-- Returns: 7

SPLIT_PART - Extract part from split string

Splits a string and returns the element at the specified position.

Syntax: SPLIT_PART(string, delimiter, position)

Example:

SELECT SPLIT_PART("apple,banana,cherry", ",", 2) AS result
FROM '{{ds_your_dataset}}' 
LIMIT 10
-- Returns: "banana"

Date and Time Functions

DATE_FORMAT - Format date as string

Formats a date value as a string with a specific format.

Syntax: DATE_FORMAT(date, format)

Example:

SELECT DATE_FORMAT(CURRENT_DATE, '%d/%m/%Y') AS formatted_date
FROM '{{ds_your_dataset}}' 
LIMIT 10

DATE_PART - Extract part of date

Extracts a specific part from a date (year, month, day, etc.).

Syntax: DATE_PART(part, date)

Example:

SELECT DATE_PART('year', CURRENT_DATE) AS current_year
FROM '{{ds_your_dataset}}' 
LIMIT 10

DATE_TRUNC - Truncate date to specific precision

Truncates a date to a specified level of precision (year, month, day, hour, etc.).

Syntax: DATE_TRUNC(precision, date)

Example:

SELECT DATE_TRUNC('day', STRPTIME("2025-02-19 14:30:00", '%Y-%m-%d %H:%M:%S')) AS result
FROM '{{ds_your_dataset}}' 
LIMIT 10
-- Returns: "2025-02-19 00:00:00"

STRPTIME - Parse string to datetime

Converts a string to a datetime using a specified format pattern.

Syntax: STRPTIME(string, format)

Common format codes:

• %Y - 4-digit year

• %m - Month (01-12)

• %d - Day (01-31)

• %H - Hour (00-23)

• %M - Minute (00-59)

Example:

-- Parse German date format
SELECT STRPTIME("19.02.2025 10:44", '%d.%m.%Y %H:%M') AS result
FROM '{{ds_your_dataset}}' 
LIMIT 10
-- Returns: 2025-02-19 10:44:00

STRFTIME - Format datetime to string

Converts a datetime to a string using a specified format pattern.

Syntax: STRFTIME(date, format)

Example:

SELECT STRFTIME(CURRENT_DATE, '%d.%m.%Y') AS result
FROM '{{ds_your_dataset}}' 
LIMIT 10
-- Returns current date in DD.MM.YYYY format

SPLIT - Split string into array

Splits a string into an array using a delimiter.

Syntax: SPLIT(string, delimiter)

Example:

SELECT SPLIT('apple,banana,cherry', ',') AS fruit_array
FROM '{{ds_your_dataset}}' 
LIMIT 10
-- Returns: ['apple', 'banana', 'cherry']

YEAR - Extract year from date

Extracts the year from a date value.

Syntax: YEAR(date)

Example:

SELECT YEAR(CURRENT_DATE) AS current_year
FROM '{{ds_your_dataset}}' 
LIMIT 10

Array Functions

UNNEST - Expand array into rows

Converts an array into individual rows, with one row per array element.

Syntax: UNNEST(array)

Example:

SELECT UNNEST(['apple', 'banana', 'cherry']) AS fruit
FROM '{{ds_your_dataset}}' 
LIMIT 10
-- Returns three rows: "apple", "banana", "cherry"

ARRAY_LENGTH - Get array size

Returns the number of elements in an array.

Syntax: ARRAY_LENGTH(array)

Example:

SELECT ARRAY_LENGTH(['apple', 'banana', 'cherry']) AS array_size
FROM '{{ds_your_dataset}}' 
LIMIT 10
-- Returns: 3

CONTAINS - Check if array contains value

Checks if an array contains a specific value.

Syntax: CONTAINS(array, value)

Example:

SELECT CONTAINS(['apple', 'banana', 'cherry'], 'banana') AS has_banana
FROM '{{ds_your_dataset}}' 
LIMIT 10
-- Returns: true

Utility Functions

NULLIF - Return NULL if values are equal

Returns NULL if two values are equal, otherwise returns the first value.

Syntax: NULLIF(value1, value2)

Example:

SELECT NULLIF(salary, 0) AS non_zero_salary
FROM '{{ds_your_dataset}}' 
LIMIT 10
-- Returns NULL if salary is 0, otherwise returns salary

Operators

|| - String concatenation

Concatenates two or more strings together.

Syntax: string1 || string2

Example:

SELECT first_name || ' ' || last_name AS full_name
FROM '{{ds_your_dataset}}' 
LIMIT 10

IN - Check if value exists in list

Checks if a value exists in a list of values.

Syntax: column IN (value1, value2, value3)

Example:

SELECT * 
FROM '{{ds_your_dataset}}' 
WHERE department IN ('Sales', 'Marketing', 'IT')
LIMIT 10

Spatial Functions (Geospatial)

Note: These functions are available for working with geographic data and spatial operations.

ST_AREA - Calculate area of geometry

Calculates the area of a polygon or other 2D geometry.

Syntax: ST_AREA(geometry)

Example:

SELECT ST_AREA(polygon_column) AS area_sqm
FROM '{{ds_your_dataset}}' 
LIMIT 10

ST_DISTANCE - Calculate distance between geometries

Calculates the distance between two geometries.

Syntax: ST_DISTANCE(geometry1, geometry2)

Example:

SELECT ST_DISTANCE(point1, point2) AS distance_m
FROM '{{ds_your_dataset}}' 
LIMIT 10

ST_CONTAINS - Check if geometry contains another

Checks if one geometry completely contains another geometry.

Syntax: ST_CONTAINS(geometry1, geometry2)

Example:

SELECT ST_CONTAINS(polygon, point) AS point_inside_polygon
FROM '{{ds_your_dataset}}' 
LIMIT 10

ST_INTERSECTS - Check if geometries intersect

Checks if two geometries intersect (overlap or share space).

Syntax: ST_INTERSECTS(geometry1, geometry2)

Example:

SELECT ST_INTERSECTS(polygon1, polygon2) AS polygons_overlap
FROM '{{ds_your_dataset}}' 
LIMIT 10

ST_POINT - Create point geometry

Creates a point geometry from X and Y coordinates.

Syntax: ST_POINT(x, y)

Example:

SELECT ST_POINT(longitude, latitude) AS location_point
FROM '{{ds_your_dataset}}' 
LIMIT 10

For a complete list of spatial functions, see the DuckDB Spatial Extension documentation.