CODEWITHSUNDEEP
sqlite
Sc-python-leaner
Sc-python-leaner

Reputation: 259

SQLite prepending zeros

Does anyone know how to prepend leading zeros to a column in sqlite? I am getting columns like 1, 2 and for our data import they need to be 3 character codes with leading zeros.

I've tried this

update LockBox
set CustomField2 = case
             when CustomField2 = '1' then '001'
             end
WHERE CustomField2 = '1';

but does not give me the correct result. I'm probably doing this wrong. I'm more of a sql server guy not a sqlite person I would do this in SQL Server

 SELECT RIGHT('000'+CAST(field AS VARCHAR(3)),3)

How can I accomplish this in SQLite. I want to update the table then export to CSV to import into our business application.

Upvotes: 0

Views: 340

Answers (2)

James K. Lowden
James K. Lowden

Reputation: 7837

Define the column as text? 

$ sqlite3 types
sqlite> create table T( i integer, c text );
sqlite> insert into T values ( '001', '001' );
sqlite> select * from T;
i           c         
----------  ----------
1           001

Upvotes: 0

forpas
forpas

Reputation: 164099

Use substr:

update LockBox
set CustomField2 = substr('000' || CustomField2, -3, 3);

provided that all values in CustomField2 are no longer than 3 digits.

Upvotes: 1

Related Questions