Introduction
In the Firebolt UI, columns with a numeric datatype are displayed with comma separators. For example, the number 123456789 is displayed as 123,456,789. This can be confusing when the columns contain IDs or other values where the commas aren’t desired. By the end of this article, you will know how to remove commas from numbers in your Firebolt UI.
TL;DR
-
Use
::TEXTto CAST numbers to text in your query results. -
Commas are only a display feature in the Firebolt UI and don’t affect data storage or external tools.
Step-by-Step Guide
All the example SQL code uses the Ultra Fast Gaming data set. To familiarize yourself with this data set, visit this link: Ultra Fast Gaming Firebolt Sample Dataset.
Step 1: Understand the Issue
Firebolt’s UI adds commas to numeric values for easier readability when dealing with large numbers. However, this formatting can be undesirable for certain fields like IDs.
- Example: The value
123456789is displayed as123,456,789in the UI.
Step 2: Cast Numeric Fields to Text
To remove the comma formatting, CAST the numeric field to a text string using ::TEXT. This conversion will ensure that the number is displayed without commas.
- Why This Works: casting the number to text prevents Firebolt from applying its default number formatting.
Example SQL code:
SELECT
playerid AS playerid_default,
playerid::text AS playerid_text,
nickname,
email
FROM
players
LIMIT 10;
The data type for the column playerid is integer, a numeric data type. This query will display playerid_default with commas and playerid_text without commas.
