Here is the most memorable solution:
USE [cpqaAnswers]
GO
CREATE TABLE [cpqa].[tbl_AP_zeropadSolution003]
(
P_Id int,
amount varchar(50)
)
INSERT INTO [cpqa].[tbl_AP_zeropadSolution003]
VALUES(1, '2340'),
(2, '4568'),
(3, '10000')
SELECT * FROM [cpqa].[tbl_AP_zeropadSolution003] -- trouble with "ints" as "chars"
SELECT * FROM [cpqa].[tbl_AP_zeropadSolution003] ORDER BY [amount] -- not right (sic)
How many places is the question ... arbitrarily pick a number, here six (6) ... but it doesn't really matter ... the objectionable sort too:
SELECT [P_Id], LEFT(REPLICATE('0', 6), 6 - LEN([amount])) + [amount] AS [zeropadded_amount] FROM [cpqa].[tbl_AP_zeropadSolution003]
Here's the good sort:
SELECT [P_Id], LEFT(REPLICATE('0', 6), 6 - LEN([amount])) + [amount] AS [zeropadded_amount] FROM [cpqa].[tbl_AP_zeropadSolution003] ORDER BY [zeropadded_amount] DESC