Monday, May 29, 2006 11:31 AM 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

Tuesday, May 30, 2006 4:01 PM 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

Wednesday, June 7, 2006 10:34 PM 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

Sunday, August 12, 2007 12:51 AM 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

Thursday, November 1, 2007 1:55 PM 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

Monday, January 11, 2010 11:32 AM 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.

# Formatting Numbers by stuffing with streamer zeros in SQL Server | Zervos

Pingback from  Formatting Numbers by stuffing with streamer zeros in SQL Server | Zervos