How to get scientific format output from a View

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to get scientific format output from a View

Author

Topic

mlu
Starting Member

3 Posts

Posted - 2011-07-25 : 12:42:01

We have a view with the following syntax:

Select cast(cast(little_value as float) as varchar) from any_table;

Values in the little_value field are very small number and we would like to display the result of the view in scientific format only.

Here's the result of the View:

Database value.........Output value
0.0000065................6.5e-006
0.000057..................5.7e-005
0.002.......................0.002

What should we do to have the 0.002 in scientific format => 2.0e-003?

Thank you.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-07-25 : 12:58:27

I'd do this in the front end, but you could write a UDF for it. It will impact performance however.

This would work for values between 0 and 1. But again, understand that it will inpact performance on large result sets.

Create Function SceintificNotation (@val float)
RETURNS varchar(12)
As
Begin
	DECLARE @exponent tinyint
	DECLARE @tempStr varchar(12)
	SET	@tempStr = Cast(@val/1000000 as varchar(12))
	SET	@exponent = Right(Cast(@val/1000000 as varchar(12)), 2) - 6
	RETURN	REPLACE(@tempStr, Right(@tempStr, 2), Right('0' + Cast(@exponent as varchar(2)), 2))
End

Can test it like this:

Declare @t table (v float)
insert	@t values(0.0000065)
insert	@t values(0.000057)
insert	@t values(0.002)

select	v, dbo.SceintificNotation(v) from @t

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-07-25 : 13:25:50

Actually, I wasn't thinking. Can do this with Convert.

select convert(varchar(32), convert(float, 0.002), 2)

select convert(varchar(32), convert(float, 0.002), 1)

mlu
Starting Member

3 Posts

Posted - 2011-07-25 : 14:03:44

Thank you Russell,

Actually, I'd like to have the best of those syntaxe:

select convert(varchar(32), convert(float, 0.002), 0)

and

select convert(varchar(32), convert(float, 0.002), 1)

with the type 1 for float, the result have a lot of 0 after de decimal point:

0.002 => 2.0000000e-003

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-07-25 : 14:21:23

Not sure what you're asking exactly.

select convert(varchar(32), convert(float, 0.002), 0) union all
select convert(varchar(32), convert(float, 0.002), 1) union all
select convert(varchar(32), convert(float, 0.002), 2)

------------------------------
0.002
2.0000000e-003
2.000000000000000e-003

(3 row(s) affected)

If you want fewer zeros, you're back to string manipulation.

mlu
Starting Member

3 Posts

Posted - 2011-07-26 : 09:01:31

Thank you Russell, I really appreciated your help.

What I would like (my wish) is to have is a type 1 "convert float parameter" where useless 0 doesn't appears just like the type 0.

As you said, I think I don't have choice to create a UDF!

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-07-26 : 09:40:21

Gotcha. In that case, a UDF is all I can think of. But again, why not do the presentation formatting in the front end?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources