Hi Experts,
I have to Pad some numbers that have decimal points and I have done it successfully as follows:
SELECT
case
when len(myValue)=1 then RIGHT('000' + CAST(CAST(myValue AS float) AS VARCHAR(5)), 2)
when len(myValue)=2 then RIGHT('000' + CAST(CAST(myValue AS float) AS VARCHAR(4)), 2)
when len(myValue)=3 then RIGHT('000' + CAST(CAST(myValue AS float) AS VARCHAR(4)), 4)
when len(myValue)=4 and myValue<10 then RIGHT('000' + CAST(CAST(myValue AS float) AS VARCHAR(4)), 5)
when len(myValue)=4 then RIGHT('000' + CAST(CAST(myValue AS float) AS VARCHAR(4)), 4)
when len(myValue)=5 then RIGHT('000' + CAST(CAST(myValue AS float) AS VARCHAR(5)), 5)
end myColumn
FROM myTable
The sample values
11.5, 5.5, 2.25, 1, 12.25
gives
11.5, 05.5, 02.25, 01, 12.25
I need a SQL function to do this for any number.
Thanks
sqlnewbie