Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQLPlus Number/Character Formats

Posted on 2004-04-23
6
Medium Priority
?
2,836 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 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
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
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

885 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