Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQLPlus Number/Character Formats

Posted on 2004-04-23
6
Medium Priority
?
2,809 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 136 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 132 total points
ID: 10901319
tru set numwidth 50
0
 
LVL 3

Assisted Solution

by:dbms_chu
dbms_chu earned 132 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

660 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