Adding leading zeroes to integer columns in the database

An interesting requirement came in today. There was this integer column which was to store 4-digit codes, like 7872, 4331, etc. The trouble was that a value like 67 had to be interpreted as 0067 too.

Now SQL Server does not allow saving data in integer (or any other numeric format) in this way, with leading zeroes. We can write complex logic to derive it, but there is an easier way. The trick is to use the LEFT function along with SUBSTRING.
Try this code snippet and check it. Do let me know if you face any issues.

— Create the table

create table b (bid int)

Β 

— Insert the integer values

insert into b select 6

insert into b select 62

insert into b select 613

Β 

— A simple select

select * from b


— A select with leading zeroes and max
length of 4 digits

SELECT

substring(

(‘0000’ + left(bid, 4)) , len(‘0000’ + left(bid, 4))-3,4)

from
b

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

4 Responses to Adding leading zeroes to integer columns in the database

  1. Binu says:

    In Oracle – SELECT LPAD(bid,4,\’0\’) from b

  2. vijred says:

    Thanks… Another simple option πŸ™‚
    RIGHT( ‘0000’ + CAST(bid as varchar), 4 )

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s