[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# convert decimal data type to integer and char(8) data types

Posted on 2008-11-12
Medium Priority
7,622 Views
How do I convert decimal data type to integer and char(8) data types

table name
titles
column name
PRICE

21.99
19.95
39.95
12.99
6.95
19.95
23.95
10
13.95

This is an example from the internet that I tried, without success
SELECT SUBSTRING(price, 1, 30) AS price
FROM titles
WHERE CONVERT(char(8))
0
Question by:mnb951
• 2

LVL 23

Assisted Solution

Steve Wales earned 225 total points
ID: 22943708
For converting to characters use the to_char function.

Syntax with examples here: http://www.techonthenet.com/oracle/functions/to_char.php

For decimal to integer what are you planning on doing with the decimal pieces since integers by definition are whole numbers.

If you want to round to the nearest whole number use the ROUND function:
http://www.techonthenet.com/oracle/functions/round_nbr.php

If you just want to chop them off use the TRUNC function:
http://www.techonthenet.com/oracle/functions/trunc_nbr.php
0

LVL 32

Assisted Solution

awking00 earned 225 total points
ID: 22944043
You can also use floor and ceil functions. Given your examples, precisely what do you want the outcome to be?
0

LVL 27

Accepted Solution

sujith80 earned 300 total points
ID: 22946370
Not clear what is the expected output you are looking for. Post your sample data and expected results.

you may try something like this.

``````SQL> select * from tbl1;

VAL
----------
21.99
19.95
39.95
12.99
6.95
19.95
23.95
10
13.95

9 rows selected.

SQL> select val, to_char(val), lpad(to_char(val), 8), round(val)
2  from tbl1;

---------- ---------------------------------------- -------- ----------
21.99 21.99                                       21.99         22
19.95 19.95                                       19.95         20
39.95 39.95                                       39.95         40
12.99 12.99                                       12.99         13
6.95 6.95                                         6.95          7
19.95 19.95                                       19.95         20
23.95 23.95                                       23.95         24
10 10                                             10         10
13.95 13.95                                       13.95         14

9 rows selected.

SQL>
``````
0

LVL 32

Expert Comment

ID: 22994412
I find it interesting that you accepted answers, but gave a grade of B, when the responders requested you to post some sample data and your expected output. Had you done so, I'm sure you would have been provided an answer worthy of a grade of A.
0

## Featured Post

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
###### Suggested Courses
Course of the Month18 days, 17 hours left to enroll