Inserting a number as a String column in a text column and SQLite stops deletes the leading zero

Question

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

When I insert this number into SQLite DB, SQLite automatically deletes the leading zero and saves the row as 79674839. Given the affinity and that the column stores TEXT, should SQLite not keep the entire row and keep the leading zero?

thanks

Answer 1

Double check your database schema. As described in Datatypes in SQLite Version 3 , a column type name affects how values ​​are processed before saving.

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

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]) 

Output:

 irtb 796 796.0 u'0796' u'0796' 

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

In my own case, I used "STR", which ends with the default INTEGER binding. I changed it to TEXT and SQLite began to respect my leading zeros.

Answer 2

Use single quotes around the number (i.e. '079674839') if it is somewhere inside the sql inline code. Also, if you are doing this programmatically, make sure that you do not go through a numerical conversion.