SQLite has a printf() function or format() function that allows us to format numbers according to a format string.

As from SQLite 3.18.0, it accepts a comma flag, which enables us to have comma separators at the thousands marks for integers.

Further work can be done to get it working with real/floating point numbers.

Example

Here’s an example of formatting an integer with a comma as the thousands separator:

SELECT printf("%,d", 123456789);

Result:

123,456,789

The format string of "%,d" is what determined this result.

Real/Floating Point Numbers

The above solution only works when returning the number without its fractional part.

Let’s apply that format string to a real number:

SELECT printf("%,d", 1234567.4567);

Result:

1,234,567

We still get commas as group separators, because our format string doesn’t include the fractional part.

Here’s how to return the fractional part:

SELECT printf("%.2f", 1234567.4567);

Result:

1234567.46

Here, the format string of "%.2f" specifies that the result should include two decimal places. We could use "%.3f" for three decimal places, and so on.

You might assume that we can do the following:

SELECT printf("%,d.2f", 1234567.4567);

Result:

1,234,567.2f

But that clearly didn’t work.

SQLite doesn’t provide us with extensive formatting capabilities for numbers. Number formatting can be quite involved, and it’s usually better to apply formatting at the application level.

That said, if you really need to do this in SQLite, you could try formatting the integer part with the commas, then concatenating that with the decimal part.

For example, suppose we have a database column called price, that returns the following result:

SELECT price FROM Products;

Result:

5457.99   
1238999.99
11.357    
3.49      
3.0001    
1234567.89
1499.5    
9.49      
149  

We could do something along the following lines to format all those numbers with a thousands separator and a decimal point:

SELECT
    printf("%,d", price) 
    || 
    substr(
    printf("%.2f", price), 
    instr(printf("%.2f", price), "."), 
    length(printf("%.2f", price)) - instr(printf("%.2f", price), ".") + 1
    )
FROM Products;

Result:

5,457.99                                                                                                                                                                                     
1,238,999.99                                                                                                                                                                                 
11.36                                                                                                                                                                                        
3.49                                                                                                                                                                                         
3.00                                                                                                                                                                                         
1,234,567.89                                                                                                                                                                                 
1,499.50                                                                                                                                                                                     
9.49                                                                                                                                                                                         
149.00   

The format() Function

SQLite 3.38.0 (released 22 Feb 2022) renamed the printf() function to format(). The original printf() name is retained as an alias for backwards compatibility.

Therefore, the first example on this page can be changed as follows:

SELECT format("%,d", 123456789);

Result:

123,456,789