PL/SQL Pattern Coding Using Builtin Functions

Hi,

 

 I have the following coding problem: 

 

A table has a VARCHAR2(40 BYTE)  column.   The column contains data values that relate to weight combined with units of measure, such as ‘8.3 OZ’.

 

Here is a example of the data:

 

5.5 OZ

6 OZ

142 G

(null)

34

50 GRAMS

22 LBS

3 OZ/2.25 OZ

 

The types of unit of measures are:

 

OZ

G

GRAMS

LBS

 

The rules are:

 

IF NULL, skip the row.

IF ‘/’ is found, skip the row.

IF the unit of measure is missing, use a unit of measure of ‘OZ’.

 

My questions are:

 

1.        Using PL/SQL, how do I search the column to find only the numeric values and decimal (if it exists)?