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,