29 May 2006 11:31 tonyrogerson

Padding a string with leading zeros

I've need to do this a lot of times when outputing a fixed length data export where for instance a numeric column is 9 characters long and you need to prefix with leading 0's. This type of data export dates back to PL/1, COBOL era.

For a two character string you can do this...

declare @number tinyint

set @number = 2

select case when len( @number ) = 1 then '0' else '' end + cast( @number as varchar(2) )

For a number that will be greater than two characters in length you can do this...

declare @number int
declare @string varchar(10)
declare @size_of_fixed_string tinyint

set @size_of_fixed_string = 10
set @number = 40

print replicate( '0', @size_of_fixed_string )

set @string = left( replicate( '0', @size_of_fixed_string ), @size_of_fixed_string - len( @number ) ) + cast( @number as varchar(10) )

print @string

 

 Filed under:

Comments

# re: Padding a string with leading zeros

30 May 2006 16:01 by reb

I think this is a little simpler:

declare @number int
declare @string varchar(10)
declare @size_of_fixed_string tinyint

set @size_of_fixed_string = 10
set @number = 40

print right(replicate('0',@size_of_fixed_string) +
cast(@number as varchar(10)),@size_of_fixed_string)

# New SQL Blog

07 June 2006 22:34 by SimonS SQL Server Stuff

On my on going list of new SQL Blogs (well its been around for a few weeks). Tony Rogerson has started...

# New SQL Blog

12 August 2007 00:51 by SimonS SQL Server Stuff

On my on going list of new SQL Blogs (well its been around for a few weeks). Tony Rogerson has started

# re: Padding a string with leading zeros

01 November 2007 13:55 by Madhivanan

First, I prefer formatting the numbers at front end

In case if it should be done using sql, I also prefer the following

declare @number int

declare @string varchar(10)

declare @size_of_fixed_string tinyint

set @size_of_fixed_string = 10

set @number = 40

set @string = right( replicate( '0', @size_of_fixed_string ) + cast( @number as varchar(10)) ,@size_of_fixed_string)

print @string

# re: Padding a string with leading zeros

11 January 2010 11:32 by ValentinoVranken

Hi Tony,

Instead of using a string truncation function to get to the expected length, why not make it the expected size straightaway?  It's one function call less and works just fine.

Here's what I mean:

declare @number int = 42;

declare @character char(1) = '0';

declare @expectedLength int = 8;

select REPLICATE(@character, @expectedLength - LEN(@number)) + CAST(@number as varchar(8)) as Result;

Regards,

Valentino.