Format "1" as "01"

Question

Format "1" as "01"

JMI

SSChampion

Points: 11831
More actions
November 18, 2008 at 10:58 am

#195967

The field data type is INT.
I want to display it always as "01","02","03" format.
What is the best way to do it?

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

riga1966 (11/18/2008)
The field is CHAR(2)
Users enter "1", "01","4","04"
I want to display it always as "01","02","03" format.
What is the best way to do it?

declare @STR char(2);

set @STR = '1';

select right('0' + ltrim(rtrim(@str)), 2);

set @STR = ' 1';

select right('0' + ltrim(rtrim(@str)), 2);

set @STR = '10'

select right('0' + ltrim(rtrim(@str)), 2);

Does this help you out?

Derek B. Bell SSC Enthusiast Points: 170 More actions · Answer 2

I would probably write an INSERT/UPDATE trigger that would update the field to '0' + field if LEN(field) = 1.

JMI SSChampion Points: 11831 More actions · Answer 3

Thank you Lynn.

So

right('0' + ltrim(rtrim(@str)), 2)

only adds leading 0 in case LEN is less than 2?

I'm just trying to understand the mechanism...

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

riga1966 (11/18/2008)
Thank you Lynn.
So
right('0' + ltrim(rtrim(@str)), 2)
only adds leading 0 in case LEN is less than 2?
I'm just trying to understand the mechanism...

Actually, it is always adding a leading '0' to the string, we are just taking the right most 2 characters. If @STR (or the column in your table) has a single character value, such as a '1', then you end up with '01'.

sqlizer SSCrazy Points: 2120 More actions · Answer 5

I guess you can try this way also.

SELECT
CASE
WHEN LEN(COLUMNNAME) <>2 then '0'+ COLUMNNAME
ELSE COLUMNNAME
END
FROM TABLENAME

Hope this helps.

JMI SSChampion Points: 11831 More actions · Answer 6

sqlizer,

Your code does not give the right results if the column is INTEGER.

Lynn's code works perfectly.

sqlizer SSCrazy Points: 2120 More actions · Answer 7

The field is CHAR(2)
Users enter "1", "01","4","04"
I want to display it always as "01","02","03" format.
What is the best way to do it?

You mentioned in the beginning that it's CHAR(2) column.

Check this out. I created table with 2 columns. 1st column has CHAR(2) datatype and 2nd column has INT data type. Inserted just 5 rows and then run query i have mentioned in the last and I guess this time you will get your result.

CREATE TABLE Test
(Col1 char(2),
Col2 int)
GO
INSERT INTO Test
SELECT '1', 1
UNION ALL
SELECT '02', 2
UNION ALL
SELECT '2', 02
UNION ALL
SELECT '3', 3
UNION ALL
SELECT '03', 03
GO
SELECT
CASE
WHEN LEN(Col1) <>2 then '0'+ Col1
ELSE Col1
END ,
'0' + CAST(Col2 AS VARCHAR(2))
FROM Test

Scalability Doug SSCertifiable Points: 5351 More actions · Answer 8

This type of question is asked frequently and I always see similar responses. The problem is this solves a single instance. Therefore, the next time this occurs, the developer or DBA retypes the code (aka reinvents the wheel). Rather, try creating a UDF that does the same thing then reuse the UDF. See below...

create function dbo.formati(@value int, @pad int, @padchar char(1) )

returns varchar(max)

as

begin

-- declare @v-2 varchar(@pad)

return (select REPLICATE( @padchar ,@pad-len(@value)) + convert(varchar,@value) )

-- return @v-2

end

go

create function dbo.formatd(@value decimal(10,2), @pad int, @padchar char(1) )

returns varchar(max)

as

begin

-- declare @v-2 varchar(@pad)

return (select REPLICATE( @padchar ,@pad-len(@value)) + convert(varchar,@value) )

-- return @v-2

end

go

select dbo.formati( MONTH('2008-09-01'),2, '0' )

select dbo.formati( 123,5, '0' )

select dbo.formatd( 123.45,8, '-' )

declare @d decimal(10,2)

select @d=9

select LEN(@d)

As you can see, not only does it pad both int and decimal types, you can also change the pad character.

DAB

JMI SSChampion Points: 11831 More actions · Answer 9

JMI

SSChampion

Points: 11831

November 18, 2008 at 12:56 pm

#900148

Sorry.

My fault.

The column is INT.

Not CHAR(2)

JMI SSChampion Points: 11831 More actions · Answer 10

SQLServerLIfer,

Thank you so much for your input.

Looks like a comprehensive solution.

I'll read about this REPLICATE thing more.

I'm not familiar with it.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 11

riga1966 (11/18/2008)
The field data type is INT.
I want to display it always as "01","02","03" format.
What is the best way to do it?

Now that you have a couple of answers, please tell us WHY you want to do this and why you think it should be done in the database instead of in the GUI. There may be an even better answer available depending on what you are actually doing this formatting for.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)