Other functions

This section covers miscellaneous ClickHouse utility functions that include::

Query formatting (e.g., formatQuery, formatQuerySingleLine)
File path manipulation (basename)
Version information (version())
Block and row numbering (blockNumber(), rowNumberInBlock())
Size formatting (formatReadableSize, formatReadableDecimalSize)
Mathematical and type-checking operations (least, greatest, isFinite, isConstant)

ClickHouse function reference

basename

Extracts the filename from a path.

Syntax:

basename(path)

Arguments:

path (String): A string containing a file path.

Returns:

The filename extracted from the path. [String]

Example:

SELECT
  basename('/home/tacos/salsa.txt') AS filename;

Result:

| filename  |
|-----------|
| salsa.txt |

This function extracts the filename portion from a given path:

If the path ends with a slash or backslash, it returns an empty string.
If there are no slashes or backslashes, it returns the original string.
Backslashes must be escaped in string literals.

Additional Examples:

SELECT
	basename('/taco/truck/menu.pdf') AS pdf_file,
	basename('guacamole\\recipe.txt') AS windows_file,
	basename('plain-taco') AS no_path;

Result:

| pdf_file | windows_file | no_path    |
|----------|--------------|------------|
| menu.pdf | recipe.txt   | plain-taco |

When using Windows-style paths, remember to escape backslashes:

SELECT
  basename('C:\\Tacos\\hot_sauce.jar') AS windows_path;

Result:

| windows_path |
|--------------|
| hot_sauce.jar|

visibleWidth

Calculates the approximate width when outputting values to the console in text format (tab-separated).

Syntax:

visibleWidth(x)

Arguments:

x (any type): The value to calculate the width for.

Returns:

The approximate width of the value when displayed in the console. [UInt64]

Details:

This function is used by the system to implement [Pretty formats].
For NULL values, it returns the width of the string ‘NULL’ in Pretty formats.

Example:

SELECT
	visibleWidth('Crunchy Taco') AS taco_width,
	visibleWidth(42) AS number_width,
	visibleWidth(NULL) AS null_width;

Result:

| taco_width | number_width | null_width |
|------------|--------------|------------|
| 12         | 2            | 4          |

In this example:

‘Crunchy Taco’ has a visible width of 12 characters.
The number 42 has a visible width of 2 characters.
NULL has a visible width of 4 characters (the length of ‘NULL’ in Pretty formats).

This function is particularly useful when formatting output or calculating column widths for display purposes.

toTypeName

Returns the name of the data type of the passed argument.

Syntax

toTypeName(x)

Arguments

x (any type): Value of any type.

Returns:

A string containing the name of the type.

Example

SELECT
	toTypeName(42) AS type_of_number,
	toTypeName('Carne asada') AS type_of_string,
	toTypeName(now()) AS type_of_now;

Result:

| type_of_number | type_of_string | type_of_now |
|----------------|----------------|-------------|
| UInt8          | String         | DateTime    |

In this example:

42 is identified as UInt8
The string ‘Carne asada’ is identified as String
The now() function returns a DateTime type

The function returns the type name exactly as it appears in the ClickHouse source code. Therefore, for some data types, it may return a name that differs from the one used in SQL syntax.

For NULL values, the function returns the type Nullable(Nothing), which corresponds to ClickHouse’s internal NULL representation.

blockSize

Returns the number of rows in the current data block.

Syntax:

blockSize()

Returns:

The number of rows in the current block. [UInt64]

Example:

SELECT
	number,
	blockSize() AS block_size
FROM
	numbers(10)

Result:

| number | block_size |
|--------|------------|
|      0 |          3 |
|      1 |          3 |
|      2 |          3 |
|      3 |          3 |
|      4 |          3 |
|      5 |          3 |
|      6 |          3 |
|      7 |          1 |
|      8 |          1 |
|      9 |          1 |

In this example, the block size is 3 to demonstrate how blockSize() returns the size of each processed block. The last block contains only one row because there are only 10 numbers in total.

The blockSize() function is primarily used for debugging and testing purposes. In production queries, the block size can vary and should not be relied upon for business logic.

byteSize

Returns an estimation of the uncompressed byte size of its arguments in memory.

Syntax:

byteSize(argument [, ...])

Arguments:

argument (any type): Value of any type.

Returns:

Estimation of byte size of the arguments in memory. (UInt64)

Examples:

For (String) arguments, the function returns the string length + 9 (terminating zero + length).

SELECT
  byteSize('salsa verde');

Result:

| byteSize('salsa verde') |
|-------------------------|
| 20                      |

Query demonstrating byteSize for various data types:

SELECT
	'tortilla' AS ingredient,
	byteSize('tortilla') AS ingredient_size,
	byteSize(1::UInt8) AS amount_size,
	byteSize(120::UInt16) AS calories_size,
	byteSize(0.50::Decimal32(2)) AS price_size,
	byteSize(false) AS is_spicy_size;

Result:

| ingredient | ingredient_size | amount_size | calories_size | price_size | is_spicy_size  |
|------------|-----------------|-------------|---------------|------------|----------------|
| tortilla   | 17              | 1           | 2             | 4          | 1              |

If the function has multiple arguments, it accumulates their byte sizes:

SELECT
  byteSize(NULL, 1, 0.3, 'guacamole');

Result:

| byteSize(NULL, 1, 0.3, 'guacamole') |
|-------------------------------------|
| 27                                  |

This function can be useful for estimating memory usage of different data structures and optimizing storage or query performance.

currentDatabase

Returns the name of the current database.

Syntax:

currentDatabase()

Returns:

The name of the current database. (String)

Example:

SELECT
  currentDatabase() AS current_db;

Result:

| current_db |
|------------|
| tacobase   |

isConstant

Checks whether the argument is a constant expression.

Syntax:

isConstant(x)

Arguments:

x: Expression to check.

Returns:

1 if x is constant. (UInt8)
0 if x is non-constant. (UInt8)

This function is primarily intended for development, debugging, and demonstration purposes.

Example:

SELECT
  isConstant(number)
FROM
  numbers(1);

Result:

| isConstant(number) |
|--------------------|
| 0                  |

In this example, isConstant returns 0 because number is not a constant expression.

SELECT
	isConstant(42) AS const_check;

Result:

| const_check |
|-------------|
| 1           |

Here, isConstant returns 1 because 42 is a constant value.

WITH
	3.14 AS pi
SELECT
	isConstant(cos(pi)) AS const_trig;

Result:

| const_trig |
|------------|
| 1          |

In this case, isConstant returns 1 because cos(pi) is a constant expression, as pi is defined as a constant in the WITH clause.

Note that isConstant evaluates the constancy of an expression during query analysis, before actual execution. This makes it useful for optimizing queries and understanding how ClickHouse processes different types of expressions.

isFinite

Checks whether a floating-point value is finite.

Syntax:

isFinite(x)

Arguments:

x (Float32 or Float64): The value to check.

Returns:

1 if x is finite, 0 otherwise. Note that 0 is returned for NaN values. [UInt8]

Example:

SELECT
	taco_price,
	isFinite(taco_price) AS is_finite_price
FROM
	taco_menu;

Result:

| taco_price | is_finite_price |
|------------|-----------------|
| 5.99       | 1               |
| inf        | 0               |
| 7.50       | 1               |

In this example, isFinite checks if each taco_price is finite. The result 1 indicates a finite price, while 0 indicates an infinite price or NaN.

isInfinite

Checks if a floating-point value is infinite.

Syntax:

isInfinite(x)

Arguments:

x (Float32 or Float64): The value to check.

Returns:

1 if x is infinite, 0 otherwise.

Note that 0 is returned for NaN values.

[UInt8]

Example:

SELECT
	taco_price,
	isInfinite(taco_price) AS is_infinite_price
FROM
	taco_menu;

Result:

| taco_price | is_infinite_price |
|------------|-------------------|
| 5.99       | 0                 |
| inf        | 1                 |
| 7.50       | 0                 |

In this example, isInfinite checks if each taco_price is infinite. The result 1 indicates an infinite price, while 0 indicates a finite price or NaN.

ifNotFinite

Checks if a value is finite and returns an alternative value if it’s not.

Syntax:

ifNotFinite(x, y)

Arguments:

x (Float32 or Float64): The value to check.
y (Float32 or Float64): The value to return if x is not finite.

Returns:

If x is finite, returns x.
If x is infinite or NaN, returns y.

Example:

SELECT
	ifNotFinite(0.0, 42.0) AS finite,
	ifNotFinite(1/0, 42.0) AS infinite,
	ifNotFinite(0/0, 42.0) AS nan
FROM
	taco_orders;

Result:

| finite | infinite | nan  |
|--------|----------|------|
| 0.0    | 42.0     | 42.0 |

In this example:

finite returns 0.0 because it’s a finite number.
infinite returns 42.0 because 1/0 is infinity.
nan returns 42.0 because 0/0 is NaN (Not a Number).

This function is useful for handling potential infinite or NaN values in floating-point calculations, especially when dealing with division operations or mathematical functions that might produce such results.

isNaN

Checks whether the argument is NaN (Not a Number).

Syntax

isNaN(x)

Arguments

x (Float32 or Float64): Value to check.

Returns:

1 if the argument is NaN.
0 otherwise.

Type: UInt8

Example

Query:

SELECT
	isNaN(0/0) AS nan_check,
	isNaN(1) AS not_nan_check,
	isNaN(0/1) AS also_not_nan_check,
	isNaN(tacos_eaten / total_tacos) AS taco_ratio_check
FROM
(
	SELECT
		5 AS tacos_eaten,
		0 AS total_tacos
);

Result:

| nan_check | not_nan_check | also_not_nan_check | taco_ratio_check  |
|-----------|---------------|---------------------|------------------|
| 1         | 0             | 0                   | 1                |

In this example:

0/0 results in NaN, so isNaN(0/0) returns 1.
1 and 0/1 are not NaN, so isNaN returns 0 for both.
tacos_eaten / total_tacos (5/0) results in a division by zero, which is NaN, so isNaN returns 1.

This function is useful for detecting undefined mathematical operations or missing data in floating-point calculations, particularly in scenarios involving consumption metrics.

bar

Builds a bar chart.

Syntax:

bar(x, min, max, width)

Arguments:

x (numeric): Size to display.
min (Int32): Minimum value for the bar.
max (Int32): Maximum value for the bar.
width (UInt8): Width of the bar in characters.

Returns:

A string representing a bar chart.

The function draws a band with width proportional to (x - min) and equal to width characters when x = max. The band is drawn with accuracy to one eighth of a symbol.

Example:

SELECT
	toHour(order_time) AS hour,
	count() AS orders,
	bar(orders, 0, 2000, 20) AS order_bar
FROM
	taco_orders
GROUP BY
	hour
ORDER BY
	hour ASC

Result:

| hour | orders | order_bar                |
|------|--------|--------------------------|
|    0 |    203 | █▋                       |
|    1 |    140 | █▎                       |
|    2 |     51 | ▍                        |
|    3 |     20 | ▏                        |
|    4 |     33 | ▎                        |
|    5 |    134 | █▏                       |
|    6 |    460 | ████▌                    |
|    7 |    748 | ███████▍                 |
|    8 |   1012 | ██████████               |
|    9 |   1342 | █████████████▍           |
|   10 |   1654 | ████████████████▌        |
|   11 |   2000 | ████████████████████     |
|   12 |   1879 | ██████████████████▊      |
|   13 |   1545 | ███████████████▍         |
|   14 |   1345 | █████████████▍           |
|   15 |   1247 | ████████████▍            |
|   16 |   1134 | ███████████▎             |
|   17 |   1002 | ██████████               |
|   18 |    878 | ████████▊                |
|   19 |    743 | ███████▍                 |
|   20 |    567 | █████▋                   |
|   21 |    412 | ████                     |
|   22 |    305 | ███                      |
|   23 |    243 | ██▍                      |

This example generates a bar chart showing the distribution of taco orders throughout the day. Each bar represents the number of orders for a given hour, with the maximum width (20 characters) corresponding to 2000 orders.

formatReadableDecimalSize

Formats a size in bytes into a human-readable string using decimal (base 10) units.

Syntax:

formatReadableDecimalSize(bytes)

Arguments:

bytes (UInt64): The number of bytes to format.

Returns:

A human-readable string representing the size with an appropriate decimal unit suffix (B, KB, MB, GB, TB, PB, or EB). [String]

Example:

SELECT
	arrayJoin([1, 1024, 1024*1024, 192851925]) AS order_payload_size_bytes,
	formatReadableDecimalSize(order_payload_size_bytes) AS order_payload_size

Result:

| order_payload_size_bytes | order_payload_size |
|--------------------------|--------------------|
| 1                        | 1.00 B            |
| 1024                     | 1.02 KB           |
| 1048576                  | 1.05 MB           |
| 192851925                | 192.85 MB         |

In this example, we format various taco order payload sizes from bytes to human-readable strings. The function automatically chooses the most appropriate unit for each value.

This function uses decimal (base 10) units, where 1 KB = 1000 bytes. For binary (base 2) units, use the formatReadableSize function instead.

formatReadableSize

Formats a size in bytes into a human-readable string with appropriate units (B, KiB, MiB, etc.).

Syntax:

formatReadableSize(x)

Alias:

FORMAT_BYTES

Arguments:

x (UInt64): Size in bytes.

Returns:

A human-readable string representing the size with appropriate units. (String)

Example:

SELECT
	arrayJoin([1, 1024, 1024*1024, 192851925]) AS filesize_bytes,
	formatReadableSize(filesize_bytes) AS filesize

Result:

| filesize_bytes | filesize    |
|----------------|-------------|
| 1              | 1.00 B      |
| 1024           | 1.00 KiB    |
| 1048576        | 1.00 MiB    |
| 192851925      | 183.92 MiB  |

In this example, we format various file sizes from bytes into human-readable formats.

This function uses binary prefixes (KiB, MiB, etc.) rather than decimal prefixes (KB, MB, etc.).

The inverse operations of this function are [parseReadableSize], [parseReadableSizeOrZero], and [parseReadableSizeOrNull].

Arguments:

x (numeric): The number to format.

Returns:

A human-readable string representation of the number with an appropriate suffix. (String)

Example:

SELECT
	number AS original_number,
	formatReadableQuantity(number) AS formatted_number
FROM
	(
		SELECT arrayJoin([1024, 1234000, 4567000000, 98765432101234]) AS number
	)

Result:

| original_number | formatted_number |
|-----------------|------------------|
| 1024            | 1.02 thousand    |
| 1234000         | 1.23 million     |
| 4567000000      | 4.57 billion     |
| 98765432101234  | 98.77 trillion   |

This function is particularly useful when displaying large numbers in a more digestible format.

The function rounds the number and chooses an appropriate suffix to make it more readable. It’s ideal for presenting statistics or large quantities in user interfaces or reports.

formatReadableTimeDelta

Formats a time interval (delta) in seconds into a human-readable string representation.

Syntax:

formatReadableTimeDelta(column[, maximum_unit, minimum_unit])

Arguments:

column (numeric) — A column with a numeric time delta in seconds.
maximum_unit (String, optional) — Maximum unit to show. Default value: years.
- Acceptable values: nanoseconds, microseconds, milliseconds, seconds, minutes, hours, days, months, years.
minimum_unit (String, optional) — Minimum unit to show. All smaller units are truncated.
- Acceptable values: nanoseconds, microseconds, milliseconds, seconds, minutes, hours, days, months, years.
- Default value: seconds if maximum_unit is seconds or bigger, nanoseconds otherwise.

Returns:

A string containing the formatted time delta with year/month/day/hour/minute/second/millisecond/microsecond/nanosecond components.

Example:

SELECT
	arrayJoin([100, 12345, 432546534]) AS elapsed,
	formatReadableTimeDelta(elapsed) AS time_delta
FROM
	(
		SELECT *
		FROM system.numbers
		LIMIT 3
	);

Result:

| elapsed   | time_delta                                                     |
|-----------|----------------------------------------------------------------|
| 100       | 1 minute and 40 seconds                                        |
| 12345     | 3 hours, 25 minutes and 45 seconds                             |
| 432546534 | 13 years, 8 months, 17 days, 7 hours, 48 minutes and 54 seconds|

This example demonstrates formatting various time deltas into readable strings.

You can also specify custom maximum and minimum units:

SELECT
	arrayJoin([100, 12345, 432546534.00000006]) AS elapsed,
	formatReadableTimeDelta(
		elapsed,
		'minutes',
		'nanoseconds'
	) AS time_delta
FROM
	(
		SELECT *
		FROM system.numbers
		LIMIT 3
	);

Result:

| elapsed              | time_delta                                          |
|----------------------|-----------------------------------------------------|
| 100.0                | 1 minute and 40 seconds                             |
| 12345.0              | 205 minutes and 45 seconds                          |
| 432546534.00000006   | 7209108 minutes, 54 seconds and 60 nanoseconds      |

In this case, the function formats the time delta using minutes as the maximum unit and nanoseconds as the minimum unit.

parseTimeDelta

Parses a string representing a time duration and converts it to a number of seconds.

Syntax:

parseTimeDelta(time_string)

Arguments:

time_string (String) — A string containing a sequence of numbers followed by time unit indicators.

Returns:

The total number of seconds represented by the input string. (Float64)

Example:

SELECT
  parseTimeDelta('2h30m') AS taco_prep_time;

Result:

| taco_prep_time |
|----------------|
| 9000           |

In this example, we calculate the preparation time for a gourmet taco recipe. The function parses ‘2h30m’ (2 hours and 30 minutes) and returns the equivalent in seconds.

Supported time units include:
- ns (nanoseconds)
- us (microseconds)
- ms (milliseconds)
- s or sec (seconds)
- m or min (minutes)
- h or hour (hours)
- d or day (days)
- w or week (weeks)
- mo or month (months, assuming 30 days per month)
- y or year (years, assuming 365 days per year)
The function is case-insensitive for unit indicators.
Multiple duration components can be combined, e.g., ‘1h30m15s’.
If the input string is invalid or cannot be parsed, the function will throw an exception.

This function is useful for converting human-readable time durations into a standardized seconds format, which can be used in calculations or comparisons involving time intervals.

least

Returns the smaller value of a and b.

Syntax:

least(a, b)

Arguments:

a (any comparable type): First value to compare.
b (any comparable type): Second value to compare. Must be of the same type as a.

Returns:

The smaller of a and b. The return type is the same as the input type.

Example:

SELECT
	least(5, 3) AS min_value,
	least('salsa', 'guacamole') AS min_string,
	least(toDate('2023-05-01'), toDate('2023-04-15')) AS min_date

Result:

| min_value | min_string | min_date   |
|-----------|------------|------------|
| 3         | guacamole  | 2023-04-15 |

In this example:

min_value returns 3, the smaller of 5 and 3.
min_string returns ‘guacamole’, which comes before ‘salsa’ lexicographically.
min_date returns the earlier date, ‘2023-04-15’.

The least function can be particularly useful in queries where you need to find the minimum value across multiple columns or when setting a lower bound for a value.

greatest

Returns the larger value of two arguments.

Syntax:

greatest(a, b)

Arguments:

a (any comparable type): First value to compare.
b (any comparable type): Second value to compare.

Returns:

The larger of the two input values.

Example:

SELECT
	greatest(5, 3) AS max_value,
	greatest('salsa', 'guacamole') AS spicier_dip;

Result:

| max_value | spicier_dip |
|-----------|-------------|
| 5         | salsa       |

In this example:

For numeric values, greatest returns 5 as it’s larger than 3.
For strings, greatest performs a lexicographical comparison, returning ‘salsa’ as it comes after ‘guacamole’ alphabetically.

The greatest function can be used with various data types, including numbers, strings, and dates. When comparing different data types, type conversion rules apply.

version

Returns the current version of ClickHouse as a string.

Syntax:

version()

Returns:

A string in the format:

major_version.minor_version.patch_version.number_of_commits_since_the_previous_stable_release

Example:

SELECT
	version()

Result:

| version() |
|-----------|
| 24.2.1.1  |

This function returns the version of the ClickHouse server currently running. It can be useful for:

Debugging version-specific issues
Ensuring compatibility with certain features
Logging or reporting purposes

If executed in the context of a distributed table, this function generates a normal column with values relevant to each shard. Otherwise, it produces a constant value.

blockNumber

Returns a monotonically increasing sequence number of the block containing the row. The returned block number is updated on a best-effort basis, i.e. it may not be fully accurate.

Syntax:

blockNumber()

Returns:

Sequence number of the data block where the row is located. [UInt64]

Example:

SELECT
	blockNumber()
FROM
	(
		SELECT
			*
		FROM
			system.numbers
		LIMIT 10
	)

Result:

| blockNumber() |
|---------------|
| 7             |
| 7             |
| 8             |
| 8             |
| 9             |
| 9             |
| 10            |
| 10            |
| 11            |
| 11            |

In this example, blockNumber() returns the sequence number of each data block processed.

rowNumberInBlock

Returns the row number within the current data block being processed.

Syntax:

rowNumberInBlock()

Returns:

The ordinal number of the row within the current data block, starting from 0. [UInt64]

Example:

SELECT
	taco_id,
	taco_name,
	rowNumberInBlock() AS row_num
FROM
	taco_orders

Result:

| taco_id | taco_name   | row_num |
|---------|-------------|---------|
| 1       | Carne Asada | 0       |
| 2       | Pollo       | 1       |
| 3       | Al Pastor   | 2       |
| 4       | Pescado     | 0       |
| 5       | Camarones   | 1       |

In this example, rowNumberInBlock() returns the row number within each block. The numbering restarts at 0 for each new block.

The result of this function depends on the affected data blocks and the order of data in the block. It’s primarily used for debugging and testing purposes.

rowNumberInAllBlocks

Returns a unique row number for each row processed by the function. The returned numbers start at 0.

Syntax:

rowNumberInAllBlocks()

Returns:

Ordinal number of the row in the data block starting from 0. [UInt64]

Example:

SELECT
	rowNumberInAllBlocks() AS row_num,
	taco_name
FROM
	taco_orders

Result:

| row_num | taco_name    |
|---------|--------------|
| 0       | Carne Asada  |
| 1       | Pollo        |
| 2       | Al Pastor    |
| 3       | Pescado      |
| 4       | Camarones    |
| 5       | Vegetariano  |

In this example, rowNumberInAllBlocks() assigns a unique number to each taco order, regardless of the block size. This can be useful for generating unique identifiers or for analyzing the overall order of rows in a query result.

The order of rows during calculation of rowNumberInAllBlocks() can differ from the order of rows returned to the user. To ensure a specific order, use an ORDER BY clause in your query.

identity

Returns the input value unchanged. This function is primarily used for debugging and testing purposes.

Syntax:

identity(x)

Arguments:

x (any type): A value of any type.

Returns:

The input value, unchanged.

Example:

SELECT
	identity(42) AS original_value,
	identity('Carne asada') AS taco_filling;

Result:

| original_value | taco_filling |
|----------------|--------------|
| 42             | Carne asada  |

This function can be useful in several scenarios:

Debugging: When you want to examine the exact value being passed through a complex query without any transformations.
Testing: To verify that data is being processed correctly without any unintended modifications.
Query optimization: The identity function can be used to cancel index usage, allowing you to test query performance with a full scan.

When analyzing a query for potential index usage, the query analyzer ignores everything within identity functions. It also disables constant folding.

countDigits

Counts the number of decimal digits needed to represent a value.

Syntax:

countDigits(x)

Arguments:

x (numeric) — An integer or decimal value.

Returns:

The number of decimal digits. Type: UInt8.

Example:

SELECT
	countDigits(42) AS regular_int,
	countDigits(1.5) AS decimal,
	countDigits(1234567890) AS large_int

Result:

| regular_int | decimal | large_int |
|-------------|---------|-----------|
| 2           | 2       | 10        |

In this example:

regular_int: 42 requires 2 digits to represent.
decimal: 1.5 is treated as 15, requiring 2 digits.
large_int: 1234567890 requires 10 digits to represent.

For decimal values, the function takes into account their scale. For example:

countDigits(42.000) = 5
countDigits(0.04200) = 4

This behavior allows you to check for decimal overflow. For instance, to check if a Decimal64 value might overflow, you can use: countDigits(x) > 18.

formatQuery

Formats a SQL query string, improving its readability.

Syntax:

formatQuery(query)

Arguments:

query (String): The SQL query to be formatted.

Returns:

The formatted query. (String)

Example:

SELECT
	formatQuery('SELECT taco_id, COUNT(*) AS taco_count FROM taco_orders WHERE spice_level > 3 GROUP BY taco_id ORDER BY taco_count DESC');

Result:

| formatQuery('SELECT taco_id, COUNT(*) AS taco_count FROM taco_orders WHERE spice_level > 3 GROUP BY taco_id ORDER BY taco_count DESC')      |
|---------------------------------------------------------------------------------------------------------------------------------------------|
| SELECT taco_id,                                                                                                                             |
|        COUNT(*) AS taco_count                                                                                                               |
| FROM taco_orders                                                                                                                            |
| WHERE spice_level > 3                                                                                                                       |
| GROUP BY taco_id                                                                                                                            |
| ORDER BY taco_count DESC                                                                                                                    |

This function improves the readability of SQL queries by adding proper indentation and line breaks. It’s particularly useful for formatting complex queries or when preparing SQL for documentation or code reviews.

If the input query is not well-formed, the function throws an exception. To return NULL instead of an exception for invalid queries, use the formatQueryOrNull() function.

formatQueryOrNull

Returns a formatted, possibly multi-line, version of the given SQL query. If the query is not well-formed, it returns NULL instead of throwing an exception.

Syntax:

formatQueryOrNull(query)

Arguments:

query (String): The SQL query to be formatted.

Returns:

The formatted query, or NULL if the input query is not well-formed. [String]

Example:

SELECT
	formatQueryOrNull('SELECT taco_id, COUNT(*) AS taco_count FROM taco_orders WHERE spice_level > 3 GROUP BY taco_id') AS formatted_query;

Result:

| formatted_query                                           |
|-----------------------------------------------------------|
| SELECT taco_id,                                           |
|        COUNT(*) AS taco_count                             |
| FROM taco_orders                                          |
| WHERE spice_level > 3                                     |
| GROUP BY taco_id                                          |

This function is useful for prettifying SQL queries or for handling potentially malformed queries without causing errors. It can be particularly helpful when working with user-generated queries or when debugging complex SQL statements in your data applications.

formatQuerySingleLine

Formats a SQL query as a single line, removing line breaks and extra whitespace.

Syntax:

formatQuerySingleLine(query)

Arguments:

query (String): The SQL query to be formatted.

Returns:

A formatted version of the input query as a single line. [String]

Example:

SELECT formatQuerySingleLine('
	SELECT
		taco_id,
		COUNT(*) AS taco_count
	FROM
		taco_orders
	GROUP BY
		taco_id
	HAVING
		taco_count > 10
	ORDER BY
		taco_count DESC
	LIMIT 5
');

Result:

| formatQuerySingleLine(...)                                                                      |
|-------------------------------------------------------------------------------------------------|
| SELECT taco_id, COUNT(*) AS taco_count FROM taco_orders GROUP BY taco_id HAVING taco_count > 10 |
| ORDER BY taco_count DESC LIMIT 5                                                                |

This function is useful for logging, debugging, or when you need to pass a query as a single line to another system or application. It preserves the semantic meaning of the query while removing unnecessary whitespace and line breaks.

If the input query is not well-formed, the function will throw an exception. To return NULL instead, use the formatQuerySingleLineOrNull() function.

formatQuerySingleLineOrNull

Formats a SQL query as a single line, removing unnecessary whitespace. If the input is not a valid query, it returns NULL instead of throwing an exception.

Syntax:

formatQuerySingleLineOrNull(query)

Arguments:

query (String): The SQL query to format.

Returns:

A formatted single-line version of the query, or NULL if the input is invalid. [String]

Example:

SELECT
	formatQuerySingleLineOrNull('SELECT count(*)
	FROM taco_orders
	WHERE spice_level > 3
	  AND order_date = today()') AS formatted_query;

Result:

| formatted_query                                                                      |
|--------------------------------------------------------------------------------------|
| SELECT count(*) FROM taco_orders WHERE (spice_level > 3) AND (order_date = today())  |

This function is useful for normalizing queries for logging, comparison, or storage purposes. It can handle invalid input gracefully, making it suitable for processing user-generated or potentially malformed queries.

Introduction

Statements

Functions

ClickHouse function reference

basename

visibleWidth

toTypeName

blockSize

byteSize

currentDatabase

isConstant

isFinite

isInfinite

ifNotFinite

isNaN

bar

formatReadableDecimalSize

formatReadableSize

formatReadableTimeDelta

parseTimeDelta

least

greatest

version

blockNumber

rowNumberInBlock

rowNumberInAllBlocks

identity

countDigits

formatQuery

formatQueryOrNull

formatQuerySingleLine

formatQuerySingleLineOrNull

Introduction

Statements

Functions

​ClickHouse function reference

​basename

​visibleWidth

​toTypeName

​blockSize

​byteSize

​currentDatabase

​isConstant

​isFinite

​isInfinite

​ifNotFinite

​isNaN

​bar

​formatReadableDecimalSize

​formatReadableSize

​formatReadableTimeDelta

​parseTimeDelta

​least

​greatest

​version

​blockNumber

​rowNumberInBlock

​rowNumberInAllBlocks

​identity

​countDigits

​formatQuery

​formatQueryOrNull

​formatQuerySingleLine

​formatQuerySingleLineOrNull

ClickHouse function reference

basename

visibleWidth

toTypeName

blockSize

byteSize

currentDatabase

isConstant

isFinite

isInfinite

ifNotFinite

isNaN

bar

formatReadableDecimalSize

formatReadableSize

formatReadableTimeDelta

parseTimeDelta

least

greatest

version

blockNumber

rowNumberInBlock

rowNumberInAllBlocks

identity

countDigits

formatQuery

formatQueryOrNull

formatQuerySingleLine

formatQuerySingleLineOrNull