Scalar functions·续

Returns the natural logarithm.

SELECT ln(total_amount) AS ln_val
FROM my_namespace.sales_data
WHERE total_amount > 0
LIMIT 5

log

Returns the logarithm of a value for a given base.

SELECT log(10.0, total_amount) AS log10_val
FROM my_namespace.sales_data
WHERE total_amount > 0
LIMIT 5

log2

Returns the base-2 logarithm.

SELECT log2(total_amount) AS log2_val
FROM my_namespace.sales_data
WHERE total_amount > 0
LIMIT 5

log10

Returns the base-10 logarithm.

SELECT log10(total_amount) AS log10_val
FROM my_namespace.sales_data
WHERE total_amount > 0
LIMIT 5

nanvl

Returns the first argument if it is not NaN, otherwise returns the second.

SELECT nanvl(0.0 / 0.0, -1.0) AS safe_val
FROM my_namespace.sales_data
LIMIT 1

pi

Returns the value of pi.

SELECT pi() AS pi_val
FROM my_namespace.sales_data
LIMIT 1

power

Raises a number to a power. Alias: pow.

SELECT power(total_amount, 2.0) AS amount_squared
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL
LIMIT 5

radians

Converts degrees to radians.

SELECT radians(180.0) AS pi_val
FROM my_namespace.sales_data
LIMIT 1

random

Returns a random float between 0 and 1.

SELECT random() AS rnd
FROM my_namespace.sales_data
LIMIT 1

round

Rounds a number to a specified number of decimal places.

SELECT round(total_amount, 2) AS rounded
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL
LIMIT 5

signum

Returns the sign of a number: -1, 0, or 1.

SELECT signum(total_amount - 500) AS sign_val
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL
LIMIT 5

sqrt

Returns the square root of a number.

SELECT sqrt(CAST(quantity AS DOUBLE)) AS sqrt_qty
FROM my_namespace.sales_data
WHERE quantity IS NOT NULL
LIMIT 5

Trigonometric functions

sin, cos, tan, asin, acos, atan, atan2, cot

SELECT sin(1.0) AS s, cos(1.0) AS c, tan(1.0) AS t,
       asin(0.5) AS as_val, acos(0.5) AS ac_val, atan(1.0) AS at_val
FROM my_namespace.sales_data
LIMIT 1

trunc

Truncates a number to a specified number of decimal places.

SELECT trunc(total_amount, 0) AS truncated
FROM my_namespace.sales_data
WHERE total_amount IS NOT NULL
LIMIT 5

Regex functions

regexp_count

Returns the number of matches of a pattern in a string.

SELECT department, regexp_count(department, '[aeiou]') AS vowels
FROM my_namespace.sales_data
WHERE department IS NOT NULL
LIMIT 5

regexp_instr

Returns the position of the first match of a pattern.

SELECT department, regexp_instr(department, '[0-9]') AS digit_pos
FROM my_namespace.sales_data
WHERE department IS NOT NULL
LIMIT 5

regexp_like

Returns true if a string matches a regular expression pattern.

SELECT department, regexp_like(department, '^[A-Z]{2}') AS starts_two_caps
FROM my_namespace.sales_data
WHERE department IS NOT NULL
LIMIT 5

regexp_match

Returns the first match of a pattern as an array.

SELECT department, regexp_match(department, '([A-Z][a-z]+)') AS first_word
FROM my_namespace.sales_data
WHERE department IS NOT NULL
LIMIT 3

regexp_replace

Replaces matches of a pattern with a replacement string.

SELECT department, regexp_replace(department, '[0-9]', '#') AS no_digits
FROM my_namespace.sales_data
WHERE department IS NOT NULL
LIMIT 5

String functions

ascii

Returns the ASCII code of the first character.

SELECT customer_id, ascii(customer_id) AS first_code
FROM my_namespace.sales_data
LIMIT 3

bit_length

Returns the length of a string in bits.

SELECT customer_id, bit_length(customer_id) AS bits
FROM my_namespace.sales_data
LIMIT 3

btrim

Trims characters from both sides of a string. Alias: trim.

SELECT btrim('  hello  ') AS trimmed
FROM my_namespace.sales_data
LIMIT 1

chr

Returns the character for a given ASCII code.

SELECT chr(65) AS letter
FROM my_namespace.sales_data
LIMIT 1

concat

Concatenates two or more strings.

SELECT concat(department, ' - ', region) AS label
FROM my_namespace.sales_data
WHERE department IS NOT NULL
LIMIT 5

concat_ws

Concatenates strings with a separator.

SELECT concat_ws('/', region, department) AS path
FROM my_namespace.sales_data
WHERE department IS NOT NULL
LIMIT 5

contains

Returns true if a string contains a substring.

SELECT customer_id, contains(department, 'Sales') AS is_sales
FROM my_namespace.sales_data
WHERE department IS NOT NULL
LIMIT 5

ends_with

Returns true if a string ends with a suffix.

SELECT customer_id, ends_with(department, 'ing') AS ends_ing
FROM my_namespace.sales_data
WHERE department IS NOT NULL
LIMIT 5

levenshtein

Returns the Levenshtein edit distance between two strings.

SELECT department, levenshtein(department, 'Engineering') AS dist
FROM my_namespace.sales_data
WHERE department IS NOT NULL
LIMIT 5

lower

Converts a string to lowercase.

SELECT lower(department) AS dept_lower
FROM my_namespace.sales_data
LIMIT 5

ltrim

Trims characters from the left side of a string.

SELECT ltrim('  hello') AS trimmed
FROM my_namespace.sales_data
LIMIT 1

octet_length

Returns the length of a string in bytes.

SELECT customer_id, octet_length(customer_id) AS bytes
FROM my_namespace.sales_data
LIMIT 3

repeat

Repeats a string a given number of times.

SELECT repeat(region, 2) AS doubled
FROM my_namespace.sales_data
LIMIT 3

replace

Replaces all occurrences of a substring.

SELECT department, replace(department, ' ', '_') AS underscored
FROM my_namespace.sales_data
WHERE department IS NOT NULL
LIMIT 5

rtrim

Trims characters from the right side of a string.

SELECT rtrim('hello  ') AS trimmed
FROM my_namespace.sales_data
LIMIT 1

split_part

Splits a string by a delimiter and returns the specified part (1-indexed).

SELECT customer_id, split_part(customer_id, '-', 1) AS first_part
FROM my_namespace.sales_data
WHERE customer_id IS NOT NULL
LIMIT 5

starts_with

Returns true if a string starts with a prefix.

SELECT customer_id, starts_with(department, 'Eng') AS is_eng
FROM my_namespace.sales_data
WHERE department IS NOT NULL
LIMIT 5

to_hex

Converts an integer to a hexadecimal string.

SELECT to_hex(255) AS hex_ff
FROM my_namespace.sales_data
LIMIT 1

upper

Converts a string to uppercase.

SELECT upper(region) AS region_upper
FROM my_namespace.sales_data
LIMIT 5

uuid

Generates a random UUID.

SELECT uuid() AS new_id
FROM my_namespace.sales_data
LIMIT 1

Unicode functions

character_length

Returns the number of characters in a string. Aliases: length, char_length.

SELECT department, character_length(department) AS len
FROM my_namespace.sales_data
WHERE department IS NOT NULL
LIMIT 5

find_in_set

Returns the position of a string within a comma-separated list.

SELECT find_in_set('North', 'South,North,East,West') AS pos
FROM my_namespace.sales_data
LIMIT 1

initcap

Capitalizes the first letter of each word.

SELECT initcap('hello world') AS capped
FROM my_namespace.sales_data
LIMIT 1

left

Returns the leftmost n characters of a string.

SELECT department, left(department, 5) AS prefix
FROM my_namespace.sales_data
WHERE department IS NOT NULL
LIMIT 5

lpad

Left-pads a string to a specified length.

SELECT region, lpad(region, 15, '.') AS padded
FROM my_namespace.sales_data
LIMIT 5

reverse

Reverses a string.

SELECT department, reverse(department) AS rev
FROM my_namespace.sales_data
WHERE department IS NOT NULL
LIMIT 5

right

Returns the rightmost n characters of a string.

SELECT department, right(department, 3) AS suffix
FROM my_namespace.sales_data
WHERE department IS NOT NULL
LIMIT 5

rpad

Right-pads a string to a specified length.

SELECT region, rpad(region, 15, '.') AS padded
FROM my_namespace.sales_data
LIMIT 5

strpos

Returns the position of a substring (1-indexed). Aliases: instr, position.

SELECT department, strpos(department, 'a') AS a_pos
FROM my_namespace.sales_data
WHERE department IS NOT NULL
LIMIT 5

substr

Returns a substring starting at a position for a given length. Alias: substring.

SELECT department, substr(department, 1, 8) AS first_eight
FROM my_namespace.sales_data
WHERE department IS NOT NULL
LIMIT 5

substr_index

Returns the substring before the n-th occurrence of a delimiter. Alias: substring_index.

SELECT customer_id, substr_index(customer_id, '-', 1) AS first_segment
FROM my_namespace.sales_data
WHERE customer_id IS NOT NULL
LIMIT 5

translate

Replaces characters in a string based on a mapping.

SELECT department, translate(department, 'aeiou', '12345') AS coded
FROM my_namespace.sales_data
WHERE department IS NOT NULL
LIMIT 5

Was this helpful?

YesNo

On this page

Docs

{"@context":"https://schema.org","@type":"TechArticle","@id":"https://developers.cloudflare.com/basin-sql/sql-reference/scalar-functions/#page","headline":"Scalar functions","description":"Reference for the scalar functions supported in Basin SQL, organized by category.","url":"https://developers.cloudflare.com/basin-sql/sql-reference/scalar-functions/","inLanguage":"en","image":"https://developers.cloudflare.com/basin-sql/sql-reference/scalar-functions/og.png?v=943b375a0e807737","dateModified":"2026-10-01","publisher":{"@type":"Organization","name":"Cloudflare","description":"One platform for your apps, agents, and workforce. Build, secure, and scale without managing infrastructure","url":"https://www.cloudflare.com/","sameAs":["https://github.com/cloudflare","https://www.linkedin.com/company/cloudflare","https://x.com/cloudflare"],"logo":{"@type":"ImageObject","url":"https://developers.cloudflare.com/logo.svg"},"address":{"@type":"PostalAddress","streetAddress":"101 Townsend St","addressLocality":"San Francisco","addressRegion":"CA","postalCode":"94107","addressCountry":"US"},"contactPoint":[{"@type":"ContactPoint","contactType":"Customer Support","url":"https://support.cloudflare.com/","availableLanguage":["English"]},{"@type":"ContactPoint","contactType":"Sales","url":"https://www.cloudflare.com/contact/","availableLanguage":["English"]}]},"isPartOf":{"@type":"WebSite","@id":"https://developers.cloudflare.com/#website","name":"Cloudflare Docs","url":"https://developers.cloudflare.com/"},"keywords":["SQL"]}

description: Troubleshoot common Basin SQL errors including query structure, type, and timeout issues. title: Troubleshooting guide image: https://developers.cloudflare.com/basin-sql/troubleshooting/og.png?v=30db96d89482c7e7

Skip to content

Documentation Index
Fetch the complete documentation index at: https://developers.cloudflare.com/basin-sql/llms.txt
Use this file to discover all available pages before exploring further.