Archives of the TeradataForum

Message Posted: Mon, 09 Nov 2009 @ 11:25:15 GMT


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Subj:		Re: Need to select only the mantissa part

From:		McCall, Glenn David

I need to select only the mantissa part from a column. Can you please let me know if there is any way I can do this in Teradata? (i.e) if I have 1234.56 in a column I need to display only 56 (Digits after the 'dot') If 7987.89 is the value in a column C1, my output should be 89.

You don't say whether you are working with decimals or floats. Also, you don't say if you want the mantissa as a fractional amount or just the digits without the decimal place. However, the following shows examples for all combinations with varying complexity.

     create table mantissa (
         d decimal(10,4),
         f double precision
     );
     insert into mantissa (d) values (3.14159);
     insert into mantissa (d) values (2.5);
     insert into mantissa (d) values (7987.89);
     insert into mantissa (d) values (1234.56);
     update mantissa
        set f = d;

     SELECT m.d,
         m.d - cast (m.d as integer) frac_d,
         m.f,
             /* Following ok in queryman, but not so good in bteq */
         m.f - cast (m.f as integer) frac_f,
         substring(m.d from position ('.' IN m.d)+1) AS frac_cd,
             /* Following Doesn't produce a nice result */
         substring(m.f from position ('.' IN m.f)+1) AS frac_cf,
             /* this works much better */
         substring(m2.d_c from position ('.' IN m2.d_c)+1) AS frac_cc
     from mantissa m join (
             select d, cast(d as varchar(20)) as d_c
             from mantissa
         ) as m2 on
             m.d = m2.d
     ;

And here is what the results look like in queryman.

     d           frac_d   f             frac_f     frac_cd   frac_cf               frac_cc
     7987.8900   0.8900   7987.890000   0.890000   8900      98789000000000E 003   8900
     2.5000      0.5000   2.500000      0.500000   5000      50000000000000E 000   5000
     1234.5600   0.5600   1234.560000   0.560000   5600      23456000000000E 003   5600
     3.1416      0.1416   3.141600      0.141600   1416      14160000000000E 000   1416

Hope this helps

Teradata Corporation
Glenn McCall
Canberra Australia


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Attachments

Library

Quick Reference