Formatting Numeric Value in Computed Column

[Stephen C. Smith]

Hi there --

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?

Thanks in advance.

[Tom van Stiphout]

On Sat, 12 Jan 2008 23:09:11 GMT, no****@nospam.com (Stephen C. Smith)
wrote:

Best to deal with that in the presentation layer, not in the data
layer.

-Tom.

>Hi there --

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?

Thanks in advance.

[Erland Sommarskog]

Stephen C. Smith (no****@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Plamen Ratchev]

Another alternative is using something like this:

SELECT REPLACE(' ' + STR(a/NULLIF(b, 0), 5, 3), ' 0.', '.') AS col_name
FROM TABLE

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[Plamen Ratchev]

Forgot to note that if negatives are expected, then it needs one more nested
REPLACE:

SELECT REPLACE(REPLACE(' ' + STR(a/NULLIF(b, 0), 5, 3), ' 0.', '.'), '-0.',
'-.') AS col_name
FROM TABLE

Plamen Ratchev
http://www.SQLStudio.com

[Stephen C. Smith]

Thanks everyone who responded. You helped a lot. Those who said to resolve it
in the presentation layer were right. I was writing the front-end in ASP.NET
but couldn't find information on the DataFormatString variable to format it the
way I want. I finally found a site which showed how to do a custom format:

{0:#.000}

.... and that did the trick.

I wasn't able to find any information before on custom formats, which was why I
was trying to do it in SQL Server.

Again, thank you to everyone who stepped forward to help.

Stephen
On Sat, 12 Jan 2008 23:09:11 GMT, no****@nospam.com (Stephen C. Smith) wrote:

>Hi there --

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?

Thanks in advance.