Format elements (original) (raw)

GoogleSQL for BigQuery supports the following format elements.

Format elements for date and time parts

Many GoogleSQL parsing and formatting functions rely on a format string to describe the format of parsed or formatted values. A format string represents the textual form of date and time and contains separate format elements that are applied left-to-right.

These functions use format strings:

• FORMAT_DATE
• FORMAT_DATETIME
• FORMAT_TIME
• FORMAT_TIMESTAMP
• PARSE_DATE
• PARSE_DATETIME
• PARSE_TIME
• PARSE_TIMESTAMP

Format strings generally support the following elements:

Examples:

SELECT FORMAT_DATE("%b-%d-%Y", DATE "2008-12-25") AS formatted;

/*-------------*
 | formatted   |
 +-------------+
 | Dec-25-2008 |
 *-------------*/

SELECT
  FORMAT_DATETIME("%c", DATETIME "2008-12-25 15:30:00")
  AS formatted;

/*--------------------------*
 | formatted                |
 +--------------------------+
 | Thu Dec 25 15:30:00 2008 |
 *--------------------------*/

SELECT FORMAT_TIME("%R", TIME "15:30:00") as formatted_time;

/*----------------*
 | formatted_time |
 +----------------+
 | 15:30          |
 *----------------*/

SELECT FORMAT_TIMESTAMP("%b %Y %Ez", TIMESTAMP "2008-12-25 15:30:00+00")
  AS formatted;

/*-----------------*
 | formatted       |
 +-----------------+
 | Dec 2008 +00:00 |
 *-----------------*/

SELECT PARSE_DATE("%Y%m%d", "20081225") AS parsed;

/*------------*
 | parsed     |
 +------------+
 | 2008-12-25 |
 *------------*/

SELECT PARSE_DATETIME('%Y-%m-%d %H:%M:%S', '1998-10-18 13:45:55') AS datetime;

/*---------------------*
 | datetime            |
 +---------------------+
 | 1998-10-18T13:45:55 |
 *---------------------*/

SELECT PARSE_TIME('%I:%M:%S %p', '2:23:38 pm') AS parsed_time

/*-------------*
 | parsed_time |
 +-------------+
 | 14:23:38    |
 *-------------*/

SELECT PARSE_TIMESTAMP("%c", "Thu Dec 25 07:30:00 2008") AS parsed;

-- Display of results may differ, depending upon the environment and
-- time zone where this query was executed.
/*-------------------------*
 | parsed                  |
 +-------------------------+
 | 2008-12-25 07:30:00 UTC |
 *-------------------------*/

Format clause for CAST

format_clause:
  FORMAT format_model

format_model:
  format_string_expression

The format clause can be used in some CAST functions. You use a format clause to provide instructions for how to conduct a cast. For example, you could instruct a cast to convert a sequence of bytes to a base64-encoded string instead of a UTF-8-encoded string.

The format clause includes a format model. The format model can contain format elements combined together as a format string.

Format bytes as string

CAST(bytes_expression AS STRING FORMAT format_string_expression)

You can cast a sequence of bytes to a string with a format element in the format string. If the bytes can't be formatted with a format element, an error is returned. If the sequence of bytes is NULL, the result is NULL. Format elements are case-insensitive.

Return type

STRING

Example

SELECT CAST(b'\x48\x65\x6c\x6c\x6f' AS STRING FORMAT 'ASCII') AS bytes_to_string;

/*-----------------*
 | bytes_to_string |
 +-----------------+
 | Hello           |
 *-----------------*/

Format string as bytes

CAST(string_expression AS BYTES FORMAT format_string_expression)

You can cast a string to bytes with a format element in the format string. If the string can't be formatted with the format element, an error is returned. Format elements are case-insensitive.

In the string expression, whitespace characters, such as \n, are ignored if the BASE64 or BASE64M format element is used.

Return type

BYTES

Example

SELECT CAST('Hello' AS BYTES FORMAT 'ASCII') AS string_to_bytes

-- Displays the bytes output value (b'\x48\x65\x6c\x6c\x6f').

/*-------------------------*
 | string_to_bytes         |
 +-------------------------+
 | b'\x48\x65\x6c\x6c\x6f' |
 *-------------------------*/

Format date and time as string

You can format these date and time parts as a string:

• Format year part as string
• Format month part as string
• Format day part as string
• Format hour part as string
• Format minute part as string
• Format second part as string
• Format meridian indicator as string
• Format time zone as string
• Format literal as string

Case matching is supported when you format some date or time parts as a string and the output contains letters. To learn more, see Case matching.

Case matching

When the output of some format element contains letters, the letter cases of the output is matched with the letter cases of the format element, meaning the words in the output are capitalized according to how the format element is capitalized. This is called case matching. The rules are:

• If the first two letters of the element are both upper case, the words in the output are capitalized. For example DAY = THURSDAY.
• If the first letter of the element is upper case, and the second letter is lowercase, the first letter of each word in the output is capitalized and other letters are lowercase. For example Day = Thursday.
• If the first letter of the element is lowercase, then all letters in the output are lowercase. For example, day = thursday.

Format year part as string

CAST(expression AS STRING FORMAT format_string_expression)

Casts a data type that contains the year part to a string. Includes format elements, which provide instructions for how to conduct the cast.

• expression: This expression contains the data type with the year that you need to format.
• format_string_expression: A string which contains format elements, including the year format element.

These data types include a year part:

• DATE
• DATETIME
• TIMESTAMP

If expression or format_string_expression is NULL the return value isNULL. If format_string_expression is an empty string, the output is an empty string. An error is generated if a value that isn't a supported format element appears in format_string_expression or expression doesn't contain a value specified by a format element.

Return type

STRING

Example

SELECT CAST(DATE '2018-01-30' AS STRING FORMAT 'YYYY') AS date_time_to_string;

/*---------------------*
 | date_time_to_string |
 +---------------------+
 | 2018                |
 *---------------------*/

Format month part as string

CAST(expression AS STRING FORMAT format_string_expression)

Casts a data type that contains the month part to a string. Includes format elements, which provide instructions for how to conduct the cast.

• expression: This expression contains the data type with the month that you need to format.
• format_string_expression: A string which contains format elements, including the month format element.

These data types include a month part:

• DATE
• DATETIME
• TIMESTAMP

If expression or format_string_expression is NULL the return value isNULL. If format_string_expression is an empty string, the output is an empty string. An error is generated if a value that isn't a supported format element appears in format_string_expression or expression doesn't contain a value specified by a format element.

Return type

STRING

Example

SELECT CAST(DATE '2018-01-30' AS STRING FORMAT 'MONTH') AS date_time_to_string;

/*---------------------*
 | date_time_to_string |
 +---------------------+
 | JANUARY             |
 *---------------------*/

Format day part as string

CAST(expression AS STRING FORMAT format_string_expression)

Casts a data type that contains the day part to a string. Includes format elements, which provide instructions for how to conduct the cast.

• expression: This expression contains the data type with the day that you need to format.
• format_string_expression: A string which contains format elements, including the day format element.

These data types include a day part:

• DATE
• DATETIME
• TIMESTAMP

If expression or format_string_expression is NULL the return value isNULL. If format_string_expression is an empty string, the output is an empty string. An error is generated if a value that isn't a supported format element appears in format_string_expression or expression doesn't contain a value specified by a format element.

Return type

STRING

Example

SELECT CAST(DATE '2018-02-15' AS STRING FORMAT 'DD') AS date_time_to_string;

/*---------------------*
 | date_time_to_string |
 +---------------------+
 | 15                  |
 *---------------------*/

Format hour part as string

CAST(expression AS STRING FORMAT format_string_expression)

Casts a data type that contains the hour part to a string. Includes format elements, which provide instructions for how to conduct the cast.

• expression: This expression contains the data type with the hour that you need to format.
• format_string_expression: A string which contains format elements, including the hour format element.

These data types include a hour part:

• TIME
• DATETIME
• TIMESTAMP

If expression or format_string_expression is NULL the return value isNULL. If format_string_expression is an empty string, the output is an empty string. An error is generated if a value that isn't a supported format element appears in format_string_expression or expression doesn't contain a value specified by a format element.

Return type

STRING

Examples

SELECT CAST(TIME '21:30:00' AS STRING FORMAT 'HH24') AS date_time_to_string;

/*---------------------*
 | date_time_to_string |
 +---------------------+
 | 21                  |
 *---------------------*/

SELECT CAST(TIME '21:30:00' AS STRING FORMAT 'HH12') AS date_time_to_string;

/*---------------------*
 | date_time_to_string |
 +---------------------+
 | 09                  |
 *---------------------*/

Format minute part as string

CAST(expression AS STRING FORMAT format_string_expression)

Casts a data type that contains the minute part to a string. Includes format elements, which provide instructions for how to conduct the cast.

• expression: This expression contains the data type with the minute that you need to format.
• format_string_expression: A string which contains format elements, including the minute format element.

These data types include a minute part:

• TIME
• DATETIME
• TIMESTAMP

If expression or format_string_expression is NULL the return value isNULL. If format_string_expression is an empty string, the output is an empty string. An error is generated if a value that isn't a supported format element appears in format_string_expression or expression doesn't contain a value specified by a format element.

Return type

STRING

Example

SELECT CAST(TIME '21:30:00' AS STRING FORMAT 'MI') AS date_time_to_string;

/*---------------------*
 | date_time_to_string |
 +---------------------+
 | 30                  |
 *---------------------*/

Format second part as string

CAST(expression AS STRING FORMAT format_string_expression)

Casts a data type that contains the second part to a string. Includes format elements, which provide instructions for how to conduct the cast.

• expression: This expression contains the data type with the second that you need to format.
• format_string_expression: A string which contains format elements, including the second format element.

These data types include a second part:

• TIME
• DATETIME
• TIMESTAMP

If expression or format_string_expression is NULL the return value isNULL. If format_string_expression is an empty string, the output is an empty string. An error is generated if a value that isn't a supported format element appears in format_string_expression or expression doesn't contain a value specified by a format element.

Return type

STRING

Examples

SELECT CAST(TIME '21:30:25.16' AS STRING FORMAT 'SS') AS date_time_to_string;

/*---------------------*
 | date_time_to_string |
 +---------------------+
 | 25                  |
 *---------------------*/

SELECT CAST(TIME '21:30:25.16' AS STRING FORMAT 'FF2') AS date_time_to_string;

/*---------------------*
 | date_time_to_string |
 +---------------------+
 | 16                  |
 *---------------------*/

Format meridian indicator part as string

CAST(expression AS STRING FORMAT format_string_expression)

Casts a data type that contains the meridian indicator part to a string. Includes format elements, which provide instructions for how to conduct the cast.

• expression: This expression contains the data type with the meridian indicator that you need to format.
• format_string_expression: A string which contains format elements, including the meridian indicator format element.

These data types include a meridian indicator part:

• TIME
• DATETIME
• TIMESTAMP

If expression or format_string_expression is NULL the return value isNULL. If format_string_expression is an empty string, the output is an empty string. An error is generated if a value that isn't a supported format element appears in format_string_expression or expression doesn't contain a value specified by a format element.

Return type

STRING

Examples

SELECT CAST(TIME '21:30:00' AS STRING FORMAT 'AM') AS date_time_to_string;
SELECT CAST(TIME '21:30:00' AS STRING FORMAT 'PM') AS date_time_to_string;

/*---------------------*
 | date_time_to_string |
 +---------------------+
 | PM                  |
 *---------------------*/

SELECT CAST(TIME '01:30:00' AS STRING FORMAT 'AM') AS date_time_to_string;
SELECT CAST(TIME '01:30:00' AS STRING FORMAT 'PM') AS date_time_to_string;

/*---------------------*
 | date_time_to_string |
 +---------------------+
 | AM                  |
 *---------------------*/

Format time zone part as string

CAST(expression AS STRING FORMAT format_string_expression)

Casts a data type that contains the time zone part to a string. Includes format elements, which provide instructions for how to conduct the cast.

• expression: This expression contains the data type with the time zone that you need to format.
• format_string_expression: A string which contains format elements, including the time zone format element.

These data types include a time zone part:

• DATE
• TIME
• DATETIME
• TIMESTAMP

If expression or format_string_expression is NULL the return value isNULL. If format_string_expression is an empty string, the output is an empty string. An error is generated if a value that isn't a supported format element appears in format_string_expression or expression doesn't contain a value specified by a format element.

Return type

STRING

Examples

SELECT CAST(TIMESTAMP '2008-12-25 00:00:00+00:00' AS STRING FORMAT 'TZH') AS date_time_to_string;

-- Results depend upon where this query was executed.
/*---------------------*
 | date_time_to_string |
 +---------------------+
 | -08                 |
 *---------------------*/

SELECT CAST(TIMESTAMP '2008-12-25 00:00:00+00:00' AS STRING FORMAT 'TZH' AT TIME ZONE 'Asia/Kolkata')
AS date_time_to_string;

-- Because the time zone is specified, the result is always the same.
/*---------------------*
 | date_time_to_string |
 +---------------------+
 | +05                 |
 *---------------------*/

SELECT CAST(TIMESTAMP '2008-12-25 00:00:00+00:00' AS STRING FORMAT 'TZM') AS date_time_to_string;

-- Results depend upon where this query was executed.
/*---------------------*
 | date_time_to_string |
 +---------------------+
 | 00                  |
 *---------------------*/

SELECT CAST(TIMESTAMP '2008-12-25 00:00:00+00:00' AS STRING FORMAT 'TZM' AT TIME ZONE 'Asia/Kolkata')
AS date_time_to_string;

-- Because the time zone is specified, the result is always the same.
/*---------------------*
 | date_time_to_string |
 +---------------------+
 | 30                  |
 *---------------------*/

Format literal as string

CAST(expression AS STRING FORMAT format_string_expression)

Format string as date and time

You can format a string with these date and time parts:

• Format string as year part
• Format string as month part
• Format string as day part
• Format string as hour part
• Format string as minute part
• Format string as second part
• Format string as meridian indicator part
• Format string as time zone part
• Format string as literal part

When formatting a string with date and time parts, you must follow theformat model rules.

Format model rules

When casting a string to date and time parts, you must ensure the _format model_is valid. The format model represents the elements passed intoCAST(string_expression AS type FORMAT format_string_expression) as theformat_string_expression and is validated according to the following rules:

• It contains at most one of each of the following parts: meridian indicator, year, month, day, hour.
• A non-literal, non-whitespace format element can't appear more than once.
• If it contains the day of year format element, DDD, then it can't contain the month.
• If it contains the 24-hour format element, HH24, then it can't contain the 12-hour format element or a meridian indicator.
• If it contains the 12-hour format element, HH12 or HH, then it must also contain a meridian indicator.
• If it contains a meridian indicator, then it must also contain a 12-hour format element.
• If it contains the second of the day format element, SSSSS, then it can't contain any of the following: hour, minute, second, or meridian indicator.
• It can't contain a format element such that the value it sets doesn't exist in the target type. For example, an hour format element such as HH24 can't appear in a string you are casting as a DATE.

Format string as year part

CAST(string_expression AS type FORMAT format_string_expression)

Casts a string-formatted year to a data type that contains the year part. Includes format elements, which provide instructions for how to conduct the cast.

• string_expression: This expression contains the string with the year that you need to format.
• type: The data type to which you are casting. Must include the year part.
• format_string_expression: A string which contains format elements, including the year format element. The formats elements in this string are defined collectively as the format model, which must followthese rules.

These data types include a year part:

• DATE
• DATETIME
• TIMESTAMP

If the YEAR part is missing from string_expression and the return type includes this part, YEAR is set to the current year.

An error is generated if a value that isn't a supported format element appears in format_string_expression or string_expression doesn't contain a value specified by a format element.

Return type

The data type to which the string was cast. This can be:

• DATE
• DATETIME
• TIMESTAMP

Examples

SELECT CAST('18-12-03' AS DATE FORMAT 'YY-MM-DD') AS string_to_date

/*----------------*
 | string_to_date |
 +----------------+
 | 2018-12-03     |
 *----------------*/

Format string as month part

CAST(string_expression AS type FORMAT format_string_expression)

Casts a string-formatted month to a data type that contains the month part. Includes format elements, which provide instructions for how to conduct the cast.

• string_expression: This expression contains the string with the month that you need to format.
• type: The data type to which you are casting. Must include the month part.
• format_string_expression: A string which contains format elements, including the month format element. The formats elements in this string are defined collectively as the format model, which must followthese rules.

These data types include a month part:

• DATE
• DATETIME
• TIMESTAMP

If the MONTH part is missing from string_expression and the return type includes this part, MONTH is set to the current month.

An error is generated if a value that isn't a supported format element appears in format_string_expression or string_expression doesn't contain a value specified by a format element.

Return type

The data type to which the string was cast. This can be:

• DATE
• DATETIME
• TIMESTAMP

Examples

SELECT CAST('DEC 03, 2018' AS DATE FORMAT 'MON DD, YYYY') AS string_to_date

/*----------------*
 | string_to_date |
 +----------------+
 | 2018-12-03     |
 *----------------*/

Format string as day part

CAST(string_expression AS type FORMAT format_string_expression)

Casts a string-formatted day to a data type that contains the day part. Includes format elements, which provide instructions for how to conduct the cast.

• string_expression: This expression contains the string with the day that you need to format.
• type: The data type to which you are casting. Must include the day part.
• format_string_expression: A string which contains format elements, including the day format element. The formats elements in this string are defined collectively as the format model, which must followthese rules.

These data types include a day part:

• DATE
• DATETIME
• TIMESTAMP

If the DAY part is missing from string_expression and the return type includes this part, DAY is set to 1.

An error is generated if a value that isn't a supported format element appears in format_string_expression or string_expression doesn't contain a value specified by a format element.

Return type

The data type to which the string was cast. This can be:

• DATE
• DATETIME
• TIMESTAMP

Examples

SELECT CAST('DECEMBER 03, 2018' AS DATE FORMAT 'MONTH DD, YYYY') AS string_to_date

/*----------------*
 | string_to_date |
 +----------------+
 | 2018-12-03     |
 *----------------*/

Format string as hour part

CAST(string_expression AS type FORMAT format_string_expression)

Casts a string-formatted hour to a data type that contains the hour part. Includes format elements, which provide instructions for how to conduct the cast.

• string_expression: This expression contains the string with the hour that you need to format.
• type: The data type to which you are casting. Must include the hour part.
• format_string_expression: A string which contains format elements, including the hour format element. The formats elements in this string are defined collectively as the format model, which must followthese rules.

These data types include a hour part:

• TIME
• DATETIME
• TIMESTAMP

If the HOUR part is missing from string_expression and the return type includes this part, HOUR is set to 0.

An error is generated if a value that isn't a supported format element appears in format_string_expression or string_expression doesn't contain a value specified by a format element.

Return type

The data type to which the string was cast. This can be:

• TIME
• DATETIME
• TIMESTAMP

Examples

SELECT CAST('15:30' AS TIME FORMAT 'HH24:MI') AS string_to_date_time

/*---------------------*
 | string_to_date_time |
 +---------------------+
 | 15:30:00            |
 *---------------------*/

Format string as minute part

CAST(string_expression AS type FORMAT format_string_expression)

Casts a string-formatted minute to a data type that contains the minute part. Includes format elements, which provide instructions for how to conduct the cast.

• string_expression: This expression contains the string with the minute that you need to format.
• type: The data type to which you are casting. Must include the minute part.
• format_string_expression: A string which contains format elements, including the minute format element. The formats elements in this string are defined collectively as the format model, which must followthese rules.

These data types include a minute part:

• TIME
• DATETIME
• TIMESTAMP

If the MINUTE part is missing from string_expression and the return type includes this part, MINUTE is set to 0.

An error is generated if a value that isn't a supported format element appears in format_string_expression or string_expression doesn't contain a value specified by a format element.

Return type

The data type to which the string was cast. This can be:

• TIME
• DATETIME
• TIMESTAMP

Examples

SELECT CAST('03:30 P.M.' AS TIME FORMAT 'HH:MI P.M.') AS string_to_date_time

/*---------------------*
 | string_to_date_time |
 +---------------------+
 | 15:30:00            |
 *---------------------*/

Format string as second part

CAST(string_expression AS type FORMAT format_string_expression)

Casts a string-formatted second to a data type that contains the second part. Includes format elements, which provide instructions for how to conduct the cast.

• string_expression: This expression contains the string with the second that you need to format.
• type: The data type to which you are casting. Must include the second part.
• format_string_expression: A string which contains format elements, including the second format element. The formats elements in this string are defined collectively as the format model, which must followthese rules.

These data types include a second part:

• TIME
• DATETIME
• TIMESTAMP

If the SECOND part is missing from string_expression and the return type includes this part, SECOND is set to 0.

An error is generated if a value that isn't a supported format element appears in format_string_expression or string_expression doesn't contain a value specified by a format element.

Return type

The data type to which the string was cast. This can be:

• TIME
• DATETIME
• TIMESTAMP

Examples

SELECT CAST('01:05:07.16' AS TIME FORMAT 'HH24:MI:SS.FF1') AS string_to_date_time

/*---------------------*
 | string_to_date_time |
 +---------------------+
 | 01:05:07.2          |
 *---------------------*/

Format string as meridian indicator part

CAST(string_expression AS type FORMAT format_string_expression)

Casts a string-formatted meridian indicator to a data type that contains the meridian indicator part. Includes format elements, which provide instructions for how to conduct the cast.

• string_expression: This expression contains the string with the meridian indicator that you need to format.
• type: The data type to which you are casting. Must include the meridian indicator part.
• format_string_expression: A string which contains format elements, including the meridian indicator format element. The formats elements in this string are defined collectively as the format model, which must followthese rules.

These data types include a meridian indicator part:

• TIME
• DATETIME
• TIMESTAMP

An error is generated if a value that isn't a supported format element appears in format_string_expression or string_expression doesn't contain a value specified by a format element.

Return type

The data type to which the string was cast. This can be:

• TIME
• DATETIME
• TIMESTAMP

Examples

SELECT CAST('03:30 P.M.' AS TIME FORMAT 'HH:MI A.M.') AS string_to_date_time

/*---------------------*
 | string_to_date_time |
 +---------------------+
 | 15:30:00            |
 *---------------------*/

Format string as time zone part

CAST(string_expression AS type FORMAT format_string_expression)

Casts a string-formatted time zone to a data type that contains the time zone part. Includes format elements, which provide instructions for how to conduct the cast.

• string_expression: This expression contains the string with the time zone that you need to format.
• type: The data type to which you are casting. Must include the time zone part.
• format_string_expression: A string which contains format elements, including the time zone format element. The formats elements in this string are defined collectively as the format model, which must followthese rules.

These data types include a time zone part:

• DATE
• TIME
• DATETIME
• TIMESTAMP

An error is generated if a value that isn't a supported format element appears in format_string_expression or string_expression doesn't contain a value specified by a format element.

Return type

The data type to which the string was cast. This can be:

• DATE
• TIME
• DATETIME
• TIMESTAMP

Examples

SELECT CAST('2020.06.03 00:00:53+00' AS TIMESTAMP FORMAT 'YYYY.MM.DD HH:MI:SSTZH') AS string_to_date_time

/*-----------------------------*
 | as_timestamp                |
 +-----------------------------+
 | 2020-06-03 00:00:53.110 UTC |
 *-----------------------------*/

Format string as literal

CAST(string_expression AS data_type FORMAT format_string_expression)

Format numeric type as string

CAST(numeric_expression AS STRING FORMAT format_string_expression)

You can cast a numeric type to a string by combining the following format elements:

• Digits
• Decimal point
• Sign
• Currency symbol
• Group separator
• Other format elements

Except for the exponent format element (EEEE), all of the format elements generate a fixed number of characters in the output, and the output is aligned by the decimal point. The first character outputs a - for negative numbers; otherwise a space. To suppress blank characters and trailing zeroes, use theFM flag.

Return type

STRING

Example

SELECT input, CAST(input AS STRING FORMAT '$999,999.999') AS output
FROM UNNEST([1.2, 12.3, 123.456, 1234.56, -12345.678, 1234567.89]) AS input

/*------------+---------------*
 |   input    |    output     |
 +------------+---------------+
 |        1.2 |        $1.200 |
 |       12.3 |       $12.300 |
 |    123.456 |      $123.456 |
 |    1234.56 |    $1,234.560 |
 | -12345.678 |  -$12,345.678 |
 | 1234567.89 |  $###,###.### |
 *------------+---------------*/

Format digits as string

The following format elements output digits. If there aren't enough digit format elements to represent the input, all digit format elements are replaced with # in the output. If there are no sign format elements, one extra space is reserved for the sign. For example, if the input is 12 and the format string is'99', then the output is ' 12', with a length of three characters.

Return type

STRING

Example

SELECT
  CAST(12 AS STRING FORMAT '999') as a,
  CAST(-12 AS STRING FORMAT '999') as b;

/*------+------*
 | a    | b    |
 +------+------+
 |   12 |  -12 |
 *------+------*/

Format decimal point as string

The following format elements output a decimal point. These format elements are mutually exclusive. At most one can appear in the format string.

Return type

STRING

Example

SELECT CAST(12.5 AS STRING FORMAT '99.99') as a;

/*--------*
 | a      |
 +--------+
 |  12.50 |
 *--------*/

Format sign as string

The following format elements output the sign (+/-). These format elements are mutually exclusive. At most one can appear in the format string.

If there are no sign format elements, one extra space is reserved for the sign. For example, if the input is 12 and the format string is'99', then the output is ' 12', with a length of three characters.

The sign appears before the number. If the format model includes a currency symbol element, then the sign appears before the currency symbol.

Return type

STRING

Example

SELECT
  CAST(12 AS STRING FORMAT 'S99') as a,
  CAST(-12 AS STRING FORMAT 'S99') as b;

/*-----+-----*
 | a   | b   |
 +-----+-----+
 | +12 | -12 |
 *-----+-----*/

Format currency symbol as string

The following format elements output a currency symbol. These format elements are mutually exclusive. At most one can appear in the format string. In the output, the currency symbol appears before the first digit or decimal point.

Return type

STRING

Example

SELECT
  CAST(12 AS STRING FORMAT '$99') as a,
  CAST(-12 AS STRING FORMAT '$99') as b;

/*------+------*
 | a    | b    |
 +------+------+
 |  <span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mn>12</mn><mi mathvariant="normal">∣</mi><mo>−</mo></mrow><annotation encoding="application/x-tex">12 | -</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="base"><span class="strut" style="height:1em;vertical-align:-0.25em;"></span><span class="mord">12∣</span><span class="mord">−</span></span></span></span>12 |
 *------+------*/

Format group separator as string

The following format elements output a group separator.

Return type

STRING

Example

SELECT CAST(1234 AS STRING FORMAT '999,999') as a;

/*----------*
 | a        |
 +----------+
 |    1,234 |
 *----------*/

Other numeric format elements

Return type

STRING

Example

SELECT CAST(-123456 AS STRING FORMAT '9.999EEEE') as a;"

/*------------*
 | a          |
 +------------+
 | -1.235E+05 |
 *------------*/

About BASE encoding

BASE encoding translates binary data in string format into a radix-X representation.

If X is 2, 8, or 16, Arabic numerals 0–9 and the Latin letters a–z are used in the encoded string. So for example, BASE16/Hexadecimal encoding results contain 0~9 and a~f.

If X is 32 or 64, the default character tables are defined inrfc 4648. When you decode a BASE string where X is 2, 8, or 16, the Latin letters in the input string are case-insensitive. For example, both "3a" and "3A" are valid input strings for BASE16/Hexadecimal decoding, and will output the same result.