Solved

SQLPlus Number/Character Formats

Posted on 2004-04-23
6
2,753 Views
Last Modified: 2008-02-07
I’ve got a numeric field consisting of 2s, 1s and 0s in a table.  I need to be able to sum the values and convert to a string but as soon as I convert the number to a string I get numbers of the form 1.0000000000000000000000000000000000E+44

I need numbers in a non scientific form so that I can take various substrings of the number.

I've tried using substr(field,0,46)
tochar(field)
and lpad(field,46,0)
but none of these work

Can anyone help?

0
Comment
Question by:welshy76
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 

Accepted Solution

by:
atch71 earned 34 total points
ID: 10901257
You might try using TO_CHAR with explict formating such as TO_CHAR(number_field, 999999) which will force the string to display in non-scientific notation.

If you could provide an example of your data, and SQL that might be helpful.

Hope that helps.
0
 
LVL 13

Assisted Solution

by:riazpk
riazpk earned 33 total points
ID: 10901319
tru set numwidth 50
0
 
LVL 3

Assisted Solution

by:dbms_chu
dbms_chu earned 33 total points
ID: 10904922
The following script creates a table, inserts rows and select the rows with a sum function.
The results of the sum are converted to char and then substr'inged.


SQL> set serverout on
SQL> set echo on
SQL>
SQL> column total_char format A30 word wrap
SQL> column digit_1_total_char format A30 word wrap
SQL> column digit_39_total_char format A30 word wrap
SQL>
SQL> create table bignum_tbl (xx number);

Table created.
SQL>
SQL> delete from bignum_tbl;

4 rows deleted.

SQL>
SQL> insert into bignum_tbl values(8888888888888888888888888888888888888888);

1 row created.

SQL>
SQL> insert into bignum_tbl values(1);

1 row created.

SQL>
SQL> insert into bignum_tbl values(2);

1 row created.

SQL>
SQL> insert into bignum_tbl values(0);

1 row created.

SQL>
SQL> select * from bignum_tbl;

        XX
----------
8.8889E+39
         1
         2
         0

SQL>
SQL> /*
DOC>-- sum xx
DOC>-- convert the sum to a char and use FM to remove leading and trailing blanks.
DOC>-- use substr to get the first digit
DOC>
DOC>*/
SQL> select to_char(sum(xx),'99999999999999999999999999999999999999999') as total_char,
  2         substr(to_char(sum(xx),'FM99999999999999999999999999999999999999999'),1,1) as digit_1_total_char,
  3         substr(to_char(sum(xx),'FM99999999999999999999999999999999999999999'),39,1) as digit_39_total_char
  4  from bignum_tbl;

TOTAL_CHAR                                                                 DIGIT_1_TOTAL_CHAR             DIGIT_39_TOTAL_CHAR
-----------------------------------------------------------           ------------------                        ------------
  8888888888888888888888888888888888888891                             8                              9



SQL>
SQL> commit;

Commit complete.
0
 
LVL 8

Expert Comment

by:annamalai77
ID: 10905737
hi there

if ur requirement is just to convert the output into a text format say 100 as hundred then u can use

select to_char(to_date(field,'J'),'JSP')
from table_name

the above query will give u the output in strings

regards
annamalai
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Via a live example, show how to take different types of Oracle backups using RMAN.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question