ORACLE正则表达式提取省市县

利用ORACLE正则表达式提取省市县

Regexp_Substr

函数效果展示

函数入参：地址，分隔符

  --获取长地址中的行政区域
  Function Get_Admin_Region(p_Address In Varchar2, p_Delimiter In Varchar2) Return Varchar2 Is
    Cursor Main_Cur Is
      Select Regexp_Substr(p_Address, '.+?(省|市|自治区|自治州|县|区)', 1, Level) Str, Level Lv_Num
        From Dual
      Connect By Level <= 3;
    v_Return Varchar2(240);
  Begin
    For c In Main_Cur Loop
      If c.Str Is Not Null Then
        If c.Lv_Num = 1 Then
          v_Return := c.Str;
        Else
          v_Return := v_Return || p_Delimiter || c.Str;
        End If;
      End If;
    End Loop;
    Return v_Return;
  Exception
    When Others Then
      Return Null;
  End Get_Admin_Region;