SQLPlus Number/Character Formats

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?

welshy76Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
atch71Connect With a Mentor Commented:
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
 
riazpkConnect With a Mentor Commented:
tru set numwidth 50
0
 
dbms_chuConnect With a Mentor Commented:
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
 
annamalai77Commented:
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
All Courses

From novice to tech pro — start learning today.