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

Recommended Answers

All 4 Replies

Check REPLICATE()

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

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

very helpful
Thanks a lot.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.