Metacat

ALTER TABLE xml_nodes ADD nodedatanumerical NUMBER(20);

UPDATE xml_nodes SET nodedatanumerical = to_number(nodedata, '999999999999999999999999999D9999999999999999999999999999')

WHERE nodedata IS NOT NULL 

AND UPPER(nodedata) = LOWER(nodedata)

AND (REGEXP_LIKE(TRIM(REPLACE(nodedata,'\n')),'(^(-|[+]|[0-9])[0-9]*[.][0-9]*)')

     OR REGEXP_LIKE(TRIM(REPLACE(nodedata,'\n')),'^(.)[0-9]+')

     OR REGEXP_LIKE(TRIM(REPLACE(nodedata,'\n')),'^(-|[+]|[0-9])[0-9]*'))

AND NOT REGEXP_LIKE(TRIM(REPLACE(nodedata,'\n')),'(-|[+])');