FORMAT

​

Syntax

​

Scalar

format(pattern [, value, ...])

​

Arguments

​

Returns

• Type: STRING
• Possible values: Formatted text.
• NULL behavior: NULL pattern returns NULL. For an accepted specifier, NULL integer/floating-point/decimal/string/date/time/timestamp values render the text null; NULL boolean values render false (FALSE with %B, uppercase with %S). Untyped NULL renders text null only when its specifier passes type validation.

​

Properties

​

Usage notes

• Pattern can vary by row; %% renders a percent sign.
• Patterns support sequential arguments, one-based positional n$ arguments, < reuse, width, precision, flags -, +, space, #, 0, comma and parentheses. %% emits percent; %n emits newline.
• Integer conversions d/o/x/X and c/C require integers. Floating-point conversions e/E/f/g/G/a/A require supported numeric types; they do not convert integer inputs to floating point, and hexadecimal floating-point formats do not accept DECIMAL. b/B requires BOOLEAN; t/T requires a date/time value. s/S accepts the scalar types listed above.
• INTEGER and INT mean the same type; STRING means VARCHAR text. NULL can be used with a supported type. Cast NULL explicitly when its type would otherwise be unclear.

​

Examples

​

Typical behavior

SELECT format('%s=%d', 'n', 7) AS "value";

​

NULL conversions

SELECT format('%s|%d|%f|%b', CAST(NULL AS STRING),CAST(NULL AS BIGINT),CAST(NULL AS DOUBLE),CAST(NULL AS BOOLEAN)) AS "value";

​

Limitations

• Malformed patterns, missing arguments, and unsupported type/conversion pairs raise errors. BINARY, ARRAY, MAP, and STRUCT values are not supported, including with %s. Integer character conversions require a valid Unicode code point. Use the function name shown here; PRINTF is not an established alias.
• With %s or %S, DECIMAL values are printed as their unscaled integer, and date/time values as their integer epoch value. Use an explicit STRING cast when you need the usual SQL text representation.