PLSQL to modify VARCHAR2 column data

I am working on an app that involves evaluating modifications made to vehicles, and does some number crunching from figures stored in an Oracle 10g database. Unfortunately, I only have a text data in the database, yet I need to work with number开发者_如何学Cs and not text. I would like to know if anyone could help me with understanding how to perform string operations on VARCHAR2 column data in an Oracle 10g database with PLSQL:

For example: I need to take a VARCHAR2 column named TOP_SPEED in a table named CARS, parse the text data in its column to break it up into two new values, and insert these new values into two new NUMBER type columns in the CARS table, TOP_SPEED_KMH and TOP_SPEED_MPH.

The data in the TOP_SPEED column is as such: eg. "153 km/h (94.62 mph)"

I want to save the value of 153.00 into the TOP_SPEED_KMH column, and the 94.62 value into TOP_SPEED_MPH column.

I think what I have to do in a query/script is this:

1. select the text data in TOP_SPEED into a local text variable
2. modify the local text variable and save the new values into two number variables
3. write back the two number variables to the corresponding TOP_SPEED_KMH and TOP_SPEED_MPH columns

Could someone please confirm that I am on the right track? I would also really appreciate any example code if anyone has the time.

Cheers

I think it's a better idea to just have the top_speed_kmh column, and get rid of the mph one. As the number of kms in a mile never changes, you can simply multiply by 0.6 to convert to miles. So you can do the same update statement as N West suggested without the mph column: UPDATE CARS SET TOP_SPEED_KMH = TO_NUMBER(SUBSTR(1, (INSTR(UPPER(TOP_SPEED), "KM/H") -1)));

And whenever you need the mph speed, just do Select top_speed_kmh*0.6 as top_speed_mph from cars;

For the parsing bit, you would probably use either REGEXP_SUBSTR or INSTR with SUBSTR

Then use TO_NUMBER to convert to number

You can either create a PL/SQL function for each parsing, returning the number value, and run an UPDATE query on the fields, or you could create a PL/SQL procedure with a cursor looping over all the data that is to be updated.

Here are som links for some of the built-ins:

http://psoug.org/reference/substr_instr.html http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/functions116.htm

You probably don't even need to do this with PL/SQL at all.

As long as the data in the column is consistent "99.99 km/h (99.99 m/h)" you could do this directly with SQL:

UPDATE CARS
SET TOP_SPEED_KMH = TO_NUMBER(SUBSTR(1, (INSTR(UPPER(TOP_SPEED), "KM/H") - 1))),
    TOP_SPEED_MPH = <similar substr/instr combination to pull the 99.99 mph out of code>;

Set-operations are typically much faster than procedural operations.

I am working on an app that involves evaluating modifications made to vehicles, and does some number crunching from figures stored in an Oracle 10g database. Unfortunately, I only have a text data in the database, yet I need to work with numbers and not text

Sounds like you should have some number columns to store these parsed out values. Instead of always calling some parsing routine (be it regexp or substr or a custom function), pass through all the data in the table(s) ONCE and populate the new number fields. You should also modify the ETL process to populate the new number fields moving forward.

If you need numbers and can parse them out, do it once (hopefully in a staging area or off hours at least) and then have the numbers you want. Now you're free to do arithmetic and everything else you'd expect from real numbers ;)

with s as
     (select '153 km/h (94.62 mph)'  ts from dual)
select 
   ts,
   to_number(substr(ts, 1, instr(ts, ' ') -1)) speed_km,
   to_number(substr(regexp_substr(ts, '\([0-9]+'), 2))  speed_mph
 from s

Thanks everyone, it was nice to be able to use everyone's input to get the answer below:

UPDATE CARS
  SET 
    CAR_TOP_SPEED_KPH = 
      to_number(substr(CAR_TOP_SPEED, 1, instr(UPPER(CAR_TOP_SPEED), ' KM/H') -1)), 
    CAR_TOP_SPEED_MPH = 
      to_number(substr(regexp_substr(CAR_TOP_SPEED, '\([0-9]+'), 2));