0

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

3
Contributors
4
Replies
20
Views
3 Years
Discussion Span
Last Post by gbhs
0

I meant a scalar function that can pad.
How do I write it?
sqlnewbie

1

Maybe this useful
SELECT LPAD(CAST(myValue AS DECIMAL(4,2)), 5, '0');
The result will be:
11.5, 5.5, 2.25, 1, 12.25
AS
11.50 05.50 02.25 01.00 12.25

Edited by AndrisP

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.