CODEWITHSUNDEEP
oracle-databasetruncate
user1777929
user1777929

Reputation: 797

How to truncate last two letters in a string and convert to number in Oracle?

I have an Oracle table that shows a column 'Received Quantity' as a string. How do I remove the last two letters in that string and convert the rest to a number?

Select receipt_number
      ,item_name
      ,received_quantity
from item_table

The result is:

+-----------+----------+-------+
|    Rcpt 01|Kool GLue |234CS  |
+-----------+----------+-------+
|    Rcpt 02|Red GLue  |10CS   |
+-----------+----------+-------+
|    Rcpt 03|Bad GLue  |1000CS |
+-----------+----------+-------+

I need help with code to remove the 'CS' in the field and convert the numbers to a number and then calculate the sum of all those numbers.

Eventually, I want to Calculate the Sum of number of Cases for all those items.

The code I'm trying is:

Select receipt_number
      ,item_name
      ,SUM (to_number(cast(received_quantity, -2)))
from item_table
group by receipt_number, item_name

Upvotes: 0

Views: 2171

Answers (3)

user330315
user330315

Reputation:

The most robust way is to simply remove any character that is not a number using regexp_replace():

Select receipt_number,
       item_name,
       SUM(to_number(regexp_replace(received_quantity, '[^0-9]', ''))) as quantity
from item_table
group by receipt_number, item_name;

That would also cleanup strings like CS1234

Upvotes: 2

Thomas G
Thomas G

Reputation: 10226

That's the way I do it 

Select receipt_number
      ,item_name
      , SUM(TO_NUMBER(TRIM(REPLACE(received_quantity,'CS'))))

from item_table

group by receipt_number, item_name

There are many others

Upvotes: 1

FLICKER
FLICKER

Reputation: 6693

You can use SUBSTRING so answer can be:

Select receipt_number
  ,item_name
  ,SUM (to_number(cast(SUBSTR(received_quantity, 1, LENGTH(received_quantity) - 2), -2)))

from item_table

group by receipt_number, item_name

Upvotes: 0

Related Questions