FORMAT_STRING

Syntax

STRING  FORMAT_STRING(STRING <format>, BIGINT|INT|SMALLINT|STRING|ARRAY <value>)

Parameters

• format: Required. A STRING that specifies the format. Supported values include the following:

  • %d: Formats a number as a simple integer.

  • %'d: Formats a number as an integer with a thousands separator.

  • %0<number>d: Formats a number as an integer with a width of <number>, left-padded with zeros.

  • %s: Formats a string.

  • %t: Converts any data type, such as an ARRAY, to a STRING.

• value: Required. The value to format. The data type can be 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. -- Displays an integer with a thousands separator.
SELECT FORMAT_STRING("%'d", 12345678L);

-- Returns 12345678. -- Displays a simple integer.
SELECT FORMAT_STRING("%d", 12345678L);

-- Returns hello,Tom. -- Formats a string.
SELECT FORMAT_STRING('hello,%s', 'Tom');

-- Returns 00123. -- Left-pads an integer with zeros to a width of 5.
SELECT FORMAT_STRING('%05d', 123);

-- Returns [1,2,3,4]. -- Converts an array to a string and outputs the 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 Overview.