Formatting Numbers

[EriRobert]

Hi,

I want to be able to return a concatenated column of two integer values formatted as:

00 / 00

I can't find an easy way to format numbers as 00. There must be a better way to do this than the bloated code below:

Case
When intNumber1 > 9 Then Cast(intNumber1 as varchar(2))
Else '0' + Cast(intNumber1 as varchar(1))
End + ' / ' +
Case
When intNumber2 > 9 Then Cast(intNumber2 as varchar(2))
Else '0' + Cast(intNumber2 as varchar(1))
End
as FormattedColumn

Any ideas?

 

[rajeevnandanmishra]

Hi Robert,

I am not sure if there is any inbuilt function to do this formating in T-SQL. But like your command the following can also be used:
----------
RIGHT('0'+CONVERT(VARCHAR, @intNumber1),2)+' / '+
RIGHT('0'+CONVERT(VARCHAR, @intNumber2),2)
----------

But you may like to check the performance by using both the queries because, your's one is using a 'case' and an 'if' for each line and mine one is using two 'right' functions for each line.



---
Raj

 

[ClaireHCM]

Another way,

select replicate('0',2-len(intnumber1))+cast(intnumber1 as varchar(2))+'/'+
replicate('0',2-len(intnumber2))+cast(intnumber2 as varchar(2))from MyTable

 

[EriRobert]

Raj and Claire,

Two superior methods - just what I wanted.

Thanks


Robert