Sqlite: integer formatting

Question

Is it possible to format an integer column for a comma as a thousandth digit separator in Sqlite? I would like the query result to be as follows.

Id Name Price 1 Product1 1,000 2 Product2 2,500 

Answer 1

SQLite has a built-in function to format the extension number, but you can create a custom function to create it.

Here is a working example. Uses integers and will require adjustment for floats.

 #import <sqlite3.h> static char *sepnum(int num, char *buf, size_t buflen, char sepch) { int len = 0, negative = 0; if (!buf || buflen == 0) return buf; if (num < 0) { negative = 1; num = abs(num); } for (int i=1;;i++) { if (buflen>len+1) buf[len++] = '0' + (num % 10); num /= 10; if (num==0) break; if ((i % 3) == 0 && buflen>len+1) buf[len++] = sepch; } if (negative && buflen>len+1) buf[len++] = '-'; for (int i=0;i<len/2;i++) { buf[len] = buf[i]; buf[i] = buf[len-i-1]; buf[len-i-1] = buf[len]; } if (buflen>len) buf[len] = '\0'; else buf[0] = '\0'; return buf; } static void nformat(sqlite3_context *context, int argc, sqlite3_value **argv) { if (argc == 1) { int num = sqlite3_value_int(argv[0]); char buf[500] = ""; if (sepnum(num, buf, sizeof(buf), ',')) { sqlite3_result_text(context, buf, -1, SQLITE_TRANSIENT); return; } } sqlite3_result_null(context); } int main(int argc, char *argv[]) { sqlite3 *db; sqlite3_open(":memory:", &db); sqlite3_stmt *stmt; sqlite3_create_function(db, "nformat", 1, SQLITE_UTF8, NULL, &nformat, NULL, NULL); sqlite3_prepare(db, "select nformat(1234)", -1, &stmt, NULL); sqlite3_step(stmt); printf("[%s]\n", sqlite3_column_text(stmt, 0)); } 

Answer 2

If your data is stored in a table and you are using SQLite version 3.8.3 or higher, then you can accomplish what you want to use a recursive CTE.

The following is a working SQL example that I'm talking about.

 WITH NBR_LIST_ETL AS ( SELECT A.*, CAST(REPLACE(NBR_LENGTH % 3, 0, 3) AS INTEGER) AS SUB_STRING_LENGTH FROM ( SELECT ID, -- INTEGER PRIMARY KEY COLUMN NBR, -- INTEGER COLUMN YOU WANT TO FORMAT W/ COMMAS LENGTH(NBR) AS NBR_LENGTH FROM NBR_LIST A -- REPLACE NBR_LIST W/ YOUR TABLE NAME ) A ), NBR_FORMAT_RECURSIVE AS ( SELECT ID, SUBSTR(NBR, 1, SUB_STRING_LENGTH) AS NBR_SEGMENT, SUBSTR(NBR, SUB_STRING_LENGTH + 1) AS NBR_REMAINING, NBR_LENGTH - SUB_STRING_LENGTH AS NBR_LENGTH FROM NBR_LIST_ETL UNION ALL SELECT ID, SUBSTR(NBR_REMAINING, 1, 3) AS NBR_SEGMENT, SUBSTR(NBR_REMAINING, 4) AS NBR_REMAINING, NBR_LENGTH - 3 AS NBR_LENGTH FROM NBR_FORMAT_RECURSIVE WHERE NBR_LENGTH >= 3 ) SELECT GROUP_CONCAT(NBR_SEGMENT) AS NBR_FORMATTED FROM NBR_FORMAT_RECURSIVE GROUP BY ID 

If your table contains the following numbers -

 NBR 25271 7 29 75438175 342 212 4758 863 2313917 

Then the query will output -

 NBR_FORMATTED 25,271 7 29 75,438,175 342 212 4,758 863 2,313,917