Project

General

Profile

Revision 4413

Added by daigle almost 13 years ago

Add nodedatanumerical column and associated population sql

View differences:

upgrade-db-to-1.5-oracle.sql
120 120
                                                                                 
121 121
CREATE SEQUENCE xml_queryresult_id_seq;
122 122

  
123
ALTER TABLE xml_nodes ADD nodedatanumerical NUMBER(20);
124

  
125
UPDATE xml_nodes SET nodedatanumerical = to_number(nodedata, '999999999999999999999999999D9999999999999999999999999999')
126
WHERE nodedata IS NOT NULL 
127
AND UPPER(nodedata) = LOWER(nodedata)
128
AND (REGEXP_LIKE(TRIM(REPLACE(nodedata,'\n')),'(^(-|[+]|[0-9])[0-9]*[.][0-9]*)')
129
     OR REGEXP_LIKE(TRIM(REPLACE(nodedata,'\n')),'^(.)[0-9]+')
130
     OR REGEXP_LIKE(TRIM(REPLACE(nodedata,'\n')),'^(-|[+]|[0-9])[0-9]*'))
131
AND NOT REGEXP_LIKE(TRIM(REPLACE(nodedata,'\n')),'(-|[+])');

Also available in: Unified diff