Truncation of trailing zeros from the fractional side of a numeric data type

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

SQL Server 2000 Forums

SQL Server Development (2000)

Truncation of trailing zeros from the fractional side of a numeric data type

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2000-09-14 : 01:20:36

Mitchell writes "My question is regarding the display of numeric datatype into a human-friendly format within the context of using SQL functions only within a single select statement of one table containing multiple records.
For instance, we have a table field declared as numeric(15, 8) which contains fractional pricing information such as 11.000000000, 11.50000000, 11.25000000, 11.40625000, etc.
The problem to be solved is one of formatting this numeric(15, 8) field with complete fidelity, into an easier to read "string" containing the exact numerals, without trailing zeros (i.e. 11.40625000 => transformed => 11.40625). And if that's not enough, 11.00000000 must be representated as 11 instead.
The reason for this, is that Visual Basic's DAO / Variant datatype readily does this mysterious transformation and I would like to reproduce this behavior within the power of SQL. I have tried to convert to float, but this introduces rounding errors (e.g. 11.40625 becomes 11.4063). Can't factor out the decimal portion and replace zeros with null strings because that will make 11.40625000 => transformed => 11.4625 incorrectly.

This is under WinNT 4.0 SP6a and SQL Server 7.0 SP2

Respectfully yours,
-M-"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-17 : 08:21:20

[code]-- prepare test data
declare @num table (i varchar(50))

insert @num
select '134.000' union all
select '1.1200' union all
select '100.00' union all
select '69' union all
select '13.' union all
select '123456789.9876543210000000000000000000000000' union all
select '0.0200'

-- do the work
SELECT i,
CASE WHEN PATINDEX('%[1-9]%', REVERSE(i)) < PATINDEX('%.%', REVERSE(i)) THEN LEFT(i, LEN(i) - PATINDEX('%[1-9]%', REVERSE(i)) + 1) ELSE LEFT(i, LEN(i) - PATINDEX('%.%', REVERSE(i))) END 'Converted'
FROM @num[/code]
Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources