oracle 非數字型轉數字型

標籤：style   blog   http   color   os   sp   on   div   log   

原文:oracle 非數字型轉數字型

   
oracle中如果一個欄位內容不全是數字型 可以通過以下方式過濾 

to_number(substr(translate(a.vital_signs_cvalues,                                  ‘9876543210.‘ || a.vital_signs_cvalues,                                  ‘9876543210.‘),                        1,                        instr(translate(a.vital_signs_cvalues,                                        ‘9876543210.‘ ||                                        a.vital_signs_cvalues,                                        ‘9876543210.‘),                              ‘.‘)) || REPLACE(substr(translate(a.vital_signs_cvalues,                                                                ‘9876543210.‘ ||                                                                a.vital_signs_cvalues,                                                                ‘9876543210.‘),                                                      instr(translate(a.vital_signs_cvalues,                                                                      ‘9876543210.‘ ||                                                                      a.vital_signs_cvalues,                                                                      ‘9876543210.‘),                                                            ‘.‘) + 1,                                                      length(translate(a.vital_signs_cvalues,                                                                       ‘9876543210.‘ ||                                                                       a.vital_signs_cvalues,                                                                       ‘9876543210.‘))),                                               ‘.‘,                                               ‘‘)) AS vital_signs_cvalues,

 

oracle 非數字型轉數字型