Solved

SQLPlus Number/Character Formats

Posted on 2004-04-23
6
2,713 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
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

910 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now