CODEWITHSUNDEEP
sql-server
Mike Polen
Mike Polen

Reputation: 3596

How do I get back a 2 digit representation of a number in SQL 2000

I have a table on SQL2000 with a numeric column and I need the select to return a 01, 02, 03...

It currently returns 1,2,3,...10,11...

Thanks.

Upvotes: 1

Views: 504

Answers (4)

Karl
Karl

Reputation: 9155

This sort of question is about the interface to the database. Really the database should return the data and your application can reformat it if it wants the data in a particular format. You shouldn't do this in the database, but out in the presentation layer.

Upvotes: 1

Hafthor
Hafthor

Reputation: 16906

where n is a positive integer between 0 and 99:

select right('0'+ltrim(str(n)),2)

or

select right(str(100+n),2)

but I like John's answer best. Single point of specification for target width, but I posted these because they are also common idioms that might work better in other situations or languages.

Upvotes: 0

Patrick Szalapski
Patrick Szalapski

Reputation: 9439

John's answer works and is generalizable to any number of digits, but I would be more comfortable with

select case when mycolumn between -9 and 9 then '0' + str(mycolumn) else str(mycolumn) end

Upvotes: 0

John Millikin
John Millikin

Reputation: 200836

Does this work?

SELECT REPLACE(STR(mycolumn, 2), ' ', '0')

From http://foxtricks.blogspot.com/2007/07/zero-padding-numeric-value-in-transact.html

Upvotes: 7

Related Questions