SQL: How to format decimals, including ones less than 1

Question

This feels like a REALLY basic question buy I have been looking around for about an hour and have yet to find a straight answer.

I have a decimal number, it represents a weight, I want it rounded and formatted in a way that is considered normal.

1.0000 to 1.00
.1900 to 0.19
-.1900 to -0.19

Right now I'm getting rid of the trailing zeros by casting as a decimal and rounding them off

CAST(TLI_Amount as decimal(18,2))

Now heres the kicker. I don't know what version of SQL I'm on, but it must be really old. Old enough that it doesn't recognize FORMAT as a function. Which is pretty much what everyone says to use. That or the leading zero get put in front of everything, but it needs to only be there for numbers < 1

So how can I get my decimals < 1 to read as 0.xx, like any normal human readable number should be.

Sorry, I really dont know. It dosn't recognize @@VERSION or SERVERPROPERTY and I'm not on any kind of sql studio. I think its called sql-anywhere — SpeedOfRound, Nov 18 '16 at 19:59

score 2 · Accepted Answer · answered Nov 18 '16 at 23:12

2

SQL Anywhere does support @@version so perhaps that's not what you're working on. But if you are, you can look up the documentation on the str() function here. In a nutshell:

select str( 1.2345, 4, 2 )
   -> 1.23
select str( 0.1234, 4, 2 )
   -> 0.12
select str( -0.1234, 5, 2 )
   -> -0.12

There is also a round function.

answered Nov 18 '16 at 23:12

Graeme Perrow

56,086
21
82
121

Wow that's incredible, exactly what I was looking for. Really baffling to me, why does a function with a name as generic as str() have such a specific function for numbers. – SpeedOfRound Nov 21 '16 at 17:10

score 1 · Answer 2 · answered Nov 18 '16 at 20:05

1

Well, how about just adding the zero in. I think SQLAnywhere uses + for string concatenation:

SELECT (CASE WHEN TLI_Amount < 1 THEN '0' ELSE 1 END) +
        CAST(CAST(TLI_Amount as decimal(18, 2)) as VARCHAR(255))
       )

Note: This assumes that TLI_Amount is positive.

answered Nov 18 '16 at 20:05

Gordon Linoff

1,242,037
58
646
786

While that would work, and is probably the most elegant way to do it programatically, it seems really silly to do for such a simple formatting requirement. Is there really no native way to format it? Did people not need their numbers formatted back then? – SpeedOfRound Nov 18 '16 at 20:11
@SpeedOfRound . . . Formatting is usually done at the application layer. For instance, I would commonly show the results in an Excel spreadsheet, which offers wide flexibility in formatting values. – Gordon Linoff Nov 18 '16 at 20:56
What happens if TLI_Amount is negative? Because that is a valid use case – SpeedOfRound Nov 18 '16 at 21:36
Then you need to use `abs()` and a bit more logic. – Gordon Linoff Nov 19 '16 at 15:10

SQL: How to format decimals, including ones less than 1

2 Answers2