Separate Cents and dollars values Oracle

user_15422149 0
Viewed 182

I'm new with oracle queries and I would like to have some help as I'm totally stuck.

I have a Products table which contains the product id, price and product name:
And I would like to separate the price for the products into dollars and cents using select oracle query.

I don't know how to figure that but I tried the TRUNC function but I'm still can't get the cents values.

Can you help me please.

Here is the DB Table:

CREATE TABLE Products (
    ProductID int,
    Price varchar(255),
    ProductName varchar(255)
);
INSERT INTO Products VALUES (1,5.17,'Apple');
INSERT INTO Products VALUES (2,6.527,'Orange');
INSERT INTO Products VALUES (3,7.12,'Watermelon');
INSERT INTO Products VALUES (4,3.25,'Pear');
INSERT INTO Products VALUES (5,4.123,'Grape');

Thanks for any help :)

oracle11g oracle
4 Answers

here is what you need.

select substr(price, 1, instr(price, '.')-1) dollars, substr(price, instr(price, '.')+1) cents,  round(substr(price, instr(price, '.')),2)* 100 cents_rounded
from products
user_60336010

PRICE as VARHCAR2? Why? It should be a NUMBER as it is a numeric value. In that case:

SQL> desc products
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PRODUCTID                                          NUMBER(38)
 PRICE                                              NUMBER
 PRODUCTNAME                                        VARCHAR2(255)

SQL> SELECT productid,
  2         productname,
  3         price,
  4         --
  5         TRUNC (price) dollars,
  6         (price - TRUNC (price)) * 100 cents
  7    FROM products;

 PRODUCTID PRODUCTNAM      PRICE    DOLLARS      CENTS
---------- ---------- ---------- ---------- ----------
         1 Apple            5,17          5         17
         2 Orange          6,527          6       52,7
         3 Watermelon       7,12          7         12
         4 Pear             3,25          3         25
         5 Grape           4,123          4       12,3

SQL>
user_90979060

Use TRUNC to get the dollar value and MOD (modulo) to get the cents value:

SELECT ProductID,
       ProductName,
       TRUNC( Price ) AS dollars,
       MOD( Price, 1 ) * 100 AS cents
FROM   Products

Which, for your sample data, outputs:

PRODUCTID | PRODUCTNAME | DOLLARS | CENTS
--------: | :---------- | ------: | ----:
        1 | Apple       |       5 |    17
        2 | Orange      |       6 |  52.7
        3 | Watermelon  |       7 |    12
        4 | Pear        |       3 |    25
        5 | Grape       |       4 |  12.3

db<>fiddle here

user_15092640

Query:

Select productid,trunc(price) As  Dollars, nvl(substr(price - trunc(price),2),0) as Cents, ProductName As Product From Products;

Output:

PRODUCTID DOLLARS CENTS Product
1 5 17 Apple
2 6 527 Orange
3 7 12 Watermelon
4 3 25 Pear
5 4 123 Grape

Description:

Here I select the ProductId and then I use trunc function to remove any decimals (to have only the DOLLARS value) and after that I use nvl function to make like a replacement to null values (In case there are no cents in number) it will be replaced by 0 and the line: substr(price - trunc(price),2) that will subtract the full price from the DOLLARS value to get the cents.

Note:
You can solve this problem by different ways and other functions check this article for more informations: Link

user_151533260
Related