Inserting a number as a String into a Text column and SQLite stills removes the leading zero

Question

I got the following number as a string: String numberString = "079674839";

When I insert this number into a SQLite DB, SQLite automatically removes the leading zero and stores the string as 79674839. Considering affinity and that the column stores TEXT, shouldn't SQLite store the whole string and keep the leading zero?

Thanks

Answer 1

Double-check your database schema. As documented on Datatypes in SQLite Version 3, the column type name affects how values are processed before being stored.

Here's a Python program to demonstrate, using an in-memory database:

import sqlite3

db = sqlite3.connect(':memory:')
val = "0796";
db.execute('CREATE TABLE test (i INTEGER, r REAL, t TEXT, b BLOB);')
db.execute('INSERT INTO test VALUES (?, ?, ?, ?);', (val, val, val, val))
res = db.execute('SELECT * FROM test');
print '\t'.join([x[0] for x in res.description])
for row in res.fetchall():
    print '\t'.join([repr(x) for x in row])

The output is:

i    r      t       b
796  796.0  u'0796' u'0796'

So, it looks like your column is actually an integer type. Take a look at the schema definition (sqlite3 database.db .schema works from the command line), look at the documentation again, and make sure you are using one of type names that map to TEXT affinity. Unknown type names get INTEGER affinity.

In my own case, I was using 'STR', which ends up with the default INTEGER affinity. I changed it to 'TEXT', and SQLite started respecting my leading zeros.

Answer 2

Use single quotes around the number, (i.e., '079674839') if it is anywhere in inline sql code. Also, if you're doing this programatically, make sure that you are not going through a numeric conversion.