|
|
Posted by Erland Sommarskog on 01/13/08 10:39
Stephen C. Smith (nospam@nospam.com) writes:
> I'm writing a SQL query that works something like this:
>
> SELECT a, b, a/b AS col_name FROM TABLE
>
> Dividing a by b can result in a value above or below 1, e.g. 1.234 or
> 0.123.
>
> If the value is below 1, I don't want the preceding zero, e.g. .123.
>
> I've been using a STR() function to format it to three decimal points,
> e.g.:
>
> STR(a/b, 5, 3) AS col_name
>
> ... but for values below 1 it'll look like 0.123.
>
> Any suggestions for how I can make this look like .123?
As Tom says, this is typically thing you should handle client-side. But
some times the only client is Query Analyzer or Mgmt Studio, in which case
that get kind of difficult.
You could do this:
SELECT a, b,
CASE WHEN b = 0 THEN NULL
WHEN abs(a) >= abs(b) THEN ltrim(str(a/b, 10, 3))
WHEN sign(a*b) = 1
THEN substring(ltrim(str(a/b, 10, 3)), 2, 10)
WHEN sign(a*b) = -1
THEN substring(ltrim(str(a/b, 10, 3)), 3, 10)
END
FROM tbl
Note: this is untested.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|