Number

We wish to format the string representation of numbers in a certain way typically for display purposes.

We will cover two common scenarios which are:

  • Scientific Notation: Specify whether scientific notation should be used in the string representation of a number.
  • Comma Separator: Add commas to separate each three digits in the string representation of large numbers.
  • Decimal Places: Specify the number of decimal places to have in the string representation of a floating point number.

This section is concerned with the string representation of numbers. See Numeric for a coverage of the display settings of numbers.

Scientific Notation

We wish to make sure that numbers are displayed “plainly” without applying scientific notation.

In this example, we wish to create a new column col_2 that is a string representation of the numeric column col_1 while ensuring that the numbers are displayed without scientific notation.

SELECT col_1,
       FORMAT('%f', col_1) col_2
FROM table_1;

Here is how this works:

  • Scientific notation is representing 1000 as 1e+03 and is often triggered automatically when displaying large numbers.
  • To obtain a string representation of a number without scientific notation, we can use the function FORMAT() while setting the format specifier to f which means decimal notation.
  • Note that this solution will only keep 6 digits after the decimal point by default. To specify the number fo digits see Decimal Places.

Comma Separator

We wish to add comma separators between each three digits in the string representation of large numbers.

In this example, we wish to create a new column col_2 that is a string representation of the numeric column col_1 with commas added between every three digits.

SELECT col_1,
       FORMAT("%'d", col_1) col_2
FROM table_1;

Here is how this works:

  • It is common to add commas after every three digits of large numbers to enhance readability e.g. 1000000 is displayed as 1,000,000.
  • To obtain a string representation of a number where every three digits are separated by a comma, we can use the function FORMAT() while setting the format specifier to d which means decimal integer.
  • We add ' before the format specifier as a flag to add commas after every three digits.

Extension: Adding Padding

SELECT col_1,
       FORMAT("%'*d",21, col_1) col_2
FROM table_1;

Here is how this works:

  • If we wish to have padding of white space characters added to the left to match the width of the largest number so that the numbers are aligned to the right. This is often desirable when displaying numbers.
  • We specify the width of the output string by add a * before the format specifier (d in this case) and then passing the desired width as an argument to FORMAT() (21 in this example).
  • Note that we need an additional step to calculate the length of the largest number in an automated way.

Decimal Places

We wish to specify the number of decimal places (after the decimal point) to display in a string representation of a floating point number.

In this example, we wish to create a new column col_2 that is a string representation of the numeric column col_1 with two decimal places after the point for each value.

SELECT col_1,
       FORMAT('%.2f', col_1) col_2
FROM table_1;

Here is how this works:

  • To obtain a string representation of a number without scientific notation, we can use the function FORMAT() while setting the format specifier to f which means decimal notation.
  • .2 specifies the minimum number of digits to the right of the decimal point.
  • If the precision is not known beforehand, we can pass it as an argument to FORMAT() as follows : FORMAT('%.*f', precision ,col_1).

Extension: String to Numeric to String

SELECT col_1,
       FORMAT('%.2f', SAFE_CAST(col_1 AS FLOAT64)) col_2
FROM table_1;

Here is how this works:

  • In case the input col_1 is a string, we will first need to convert it to a numeric data type, so we may round it.
  • We use SAFE_CAST() to convert a string to a float data type. See Numeric.
  • We then apply round and format as covered in the primary solution above.
SQL
I/O