This should work:
SELECT STUFF(CAST(Val AS VARCHAR(10)),CASE WHEN Val<0 THEN 2 ELSE 1 END,0,REPLICATE('0',10-LEN(CAST(Val AS VARCHAR(10)))))
but it performs the
CAST
twice, which I don't like. I would use a Common Table Expression to eliminate the duplicated effort.
WITH cte0 AS
(
SELECT -45.10 Val
)
, cte1 AS
(
SELECT Val , CAST(Val AS VARCHAR(10)) ValStr FROM cte0
)
SELECT STUFF(ValStr,CASE WHEN Val<0 THEN 2 ELSE 1 END,0,REPLICATE('0',10-LEN(ValStr)))
FROM cte1
Or, if you can upgrade, you can use the FORMAT function:
SELECT FORMAT(-45.10,'000000.00')