FORMAT_STRING converts a value to a formatted string using the specified format string.
Syntax
STRING FORMAT_STRING(STRING <format>, BIGINT|INT|SMALLINT|STRING|ARRAY <value>)Parameters
format
Required. A STRING that specifies the output format. Supported format specifiers:
| Specifier | Description | Example output |
|---|---|---|
%d |
Integer | 12345678 |
%'d |
Integer with thousands separator | 12,345,678 |
%0<n>d |
Integer left-padded with zeros to width <n> |
00123 (for n=5) |
%s |
String | hello,Tom |
%t |
Any data type converted to a string | [1,2,3,4] |
value
Required. The value to format. Accepted data types: BIGINT, INT, SMALLINT, STRING, or ARRAY.
Return value
Returns a STRING. If format or value is NULL, an error is returned.
Examples
-- Returns 12,345,678 (integer with thousands separator)
SELECT FORMAT_STRING("%'d", 12345678L);
-- Returns 12345678 (simple integer)
SELECT FORMAT_STRING("%d", 12345678L);
-- Returns hello,Tom (formatted string)
SELECT FORMAT_STRING('hello,%s', 'Tom');
-- Returns 00123 (integer left-padded with zeros to width 5)
SELECT FORMAT_STRING('%05d', 123);
-- Returns [1,2,3,4] (array converted to string)
WITH DATA AS (SELECT ARRAY(1, 2, 3, 4) AS numbers)
SELECT FORMAT_STRING('%t', numbers) AS formatted_numbers FROM DATA;Related functions
FORMAT_STRING is a string function. For more information about functions for finding strings and converting string formats, see String functions.