sql query to append or - and leading zeros-CodePudding

trying to query a table that stores prices but need a way to append if number is >1 and - if <1 and append up to 8 zeros after the or - sign , and round to 2 decimal places so if table contains

Current table: prices 1000.3334

Expected return: prices 00001000.33

Tried the following query but it does not round to 2 decimal places and does not add the or - sign

SELECT RIGHT('00000000'  CONVERT(VARCHAR,prices),11) AS NUM FROM SALES;

CodePudding user response：

While I agree with others, yuck, this belongs in your front end / presentation layer:

SELECT CASE WHEN prices < 0 THEN '-' ELSE ' ' END 
      RIGHT(CONCAT(REPLICATE('0',9),
            ABS(CONVERT(decimal(11,2),prices))), 11)
  FROM dbo.sales;

Working example in this fiddle.