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 |

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

​

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.

​

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.

​

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.

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.

​

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.

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.

​

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.

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’.

​

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.

​

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

​

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.

​

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.

​

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:

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

​

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.

​

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.

​

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.

​

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.