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
(‘0000’ + left(bid, 4)) , len(‘0000’ + left(bid, 4))-3,4)