Oracle Regular intercept String function

Now there is a demand, the database of a hand to lose the ' birthplace ' field, in accordance with a certain standard interception display on the report, for example, if the ' birthplace ' of the content is: ' Shandong province Weifang Changle County ', then the report to be shown as: ' Shandong Changle ', if ' hometown ' is Shandong province Weifang Hanting District then will come out Shandong Weifang, the basic needs of simplification is this, take the provincial and county level, if the district is a zone, then take to the municipal level,

No county level, is the data is not standardized, but first will take the provincial and municipal level.

To achieve this, the first is to remove the corresponding fields from the database, and the second is to write an Oracle intercept string function to handle the ' native place ' field.

The first step is simple, just skip it, just look at our intercept function.

Create or Replace function f_addr (address in varchar2) return varchar2 as New_addr varchar2 (100); TEMPSTR1 Gscodeitems.name%type; TEMPSTR2 varchar2 (100); TEMPSTR3 varchar2 (100); TEMPSTR4 varchar2 (100); Tempn0 number; TEMPN1 number; TEMPN2 number; TEMPN3 number; TEMPNN number; Cursor Minzustrs is the select name from gscodeitems where gscodeitems.codesetnm = ' 411cbc8f-35d8-4634-944b-0c1065782f48 '; Begin/* Word County */if Regexp_count (address, ' ^ ([^ Provincial]+) (province | (. Autonomous Regions | municipalities) ((. + City |). + region |. + states)? ([^ Clan counties]) (county)) $ ') > 0 then new_addr: = Regexp_replace (Address, ' ^ ([^ Provincial]+) (province | (. Autonomous Regions | municipalities) ((. + City |). + region |. + states)? ([^ Clan counties]) (county)) $ ', ' \1\6 County '); /* Xinjiang Uyghur */elsif Regexp_count (address, ' ^ Xinjiang ') > 0 then new_addr: = Regexp_replace (Address, ' ^ (Xinjiang) (Uyghur Autonomous Region) ((. + City |). + region |. + states)? ([^ Clan]+) (County | city |. Ethnic Autonomous Counties) | ((. +) city)? ([^ Clan]+) (Zone | flag)) $ ', ' \1\5\8 '); /* Special deal with the ethnic group of two words in the former, the strategy is to two words of the nation to kill, restore to normal mode */elsif regexp_count (Address, ' ([^ clan]+) Family ', 4) >= 2 then tempstr1: = regexp_s UBSTR (Address, ' ([^ Family]{2}) family ', 3); For minzustr in Minzustrs loop if tempstr1 = Minzustr.name Then TEMPNN: = 1;     Exit     else TEMPNN: = 0; End If;     End Loop;     If Tempnn = 1 then tempn0: = Length (TEMPSTR1);     TEMPN1: = Regexp_instr (ADDRESS,TEMPSTR1);     TEMPN2: = tempn1-1;     TEMPN3: = tempn1 + tempn0;     TEMPSTR2: = substr (ADDRESS,0,TEMPN2);     TEMPSTR3: = substr (address, TEMPN3);     TEMPSTR4: = concat (TEMPSTR2,TEMPSTR3); NEW_ADDR: = Regexp_replace (TEMPSTR4, ' ^ ([^ Provincial]+) (province | (. Autonomous Regions | municipalities) ((. + City |). + region |. + states)? ([^ Clan]+) (County | city |. Ethnic Autonomous Counties) | ((. +) city)? ([^ Clan]+) (Zone | flag))   $ ',
   ' \1\6\9 ');       else TEMPSTR1: = regexp_substr (Address, ' ([^ family]) family ', 3);     Tempn0: = Length (TEMPSTR1);     TEMPN1: = Regexp_instr (ADDRESS,TEMPSTR1);     TEMPN2: = tempn1-1;     TEMPN3: = tempn1 + tempn0;     TEMPSTR2: = substr (ADDRESS,0,TEMPN2);     TEMPSTR3: = substr (address, TEMPN3);     TEMPSTR4: = concat (TEMPSTR2,TEMPSTR3); NEW_ADDR: = Regexp_replace (TEMPSTR4, ' ^ ([^ Provincial]+) (province | (. Autonomous Regions | municipalities) ((. + City |). + region |. + states)? ([^ Clan]+) (County | city |. Ethnic Autonomous Counties) | ((. +) city)? ([^ Clan]+) (Zone | flag))         $ ', ' \1\6\9 '); End If; /* Special deal with a single ethnic group Two words of the nation, first to kill Guangxi and Ningxia, and then take the first two words to judge whether it is legal, if lawful, kill a word, and then go the normal process */elsif Regexp_count (Address, ' ([^ family]+) Family ', 4) = 1 Then  TEMPSTR1: = regexp_substr (Address, ' ([^ Family]{2}) family ', 3); For minzustr in Minzustrs loop if tempstr1 = Minzustr.name then tempnn: = 1;     Exit     else TEMPNN: = 0; End If;     End Loop;     If Tempnn = 1 then tempn0: = Length (TEMPSTR1);     TEMPN1: = Regexp_instr (ADDRESS,TEMPSTR1);     TEMPN2: = tempn1-1;     TEMPN3: = tempn1 + tempn0-2; TEMPSTR2: = substr (address,0,tEMPN2);     TEMPSTR3: = substr (address, TEMPN3);     TEMPSTR4: = concat (TEMPSTR2,TEMPSTR3); NEW_ADDR: = Regexp_replace (TEMPSTR4, ' ^ ([^ Provincial]+) (province | (. Autonomous Regions | municipalities) ((. + City |). + region |. + states)? ([^ Clan]+) (County | city |. Ethnic Autonomous Counties) | ((. +) city)? ([^ Clan]+) (Zone | flag))  $ ',
' \1\6\9 '); else new_addr: = Regexp_replace (Address, ' ^ ([^ Provincial]+) (province | (. Autonomous Regions | municipalities) ((. + City |). + region |. + states)? ([^ Clan]+) (County | city |. Ethnic Autonomous Counties) | ((. +) city)? ([^ Clan]+) (Zone | flag)) $ ',
' \1\6\9 '); End If; else new_addr: = Regexp_replace (Address, ' ^ ([^ Provincial]+) (province | (. Autonomous Regions | municipalities) ((. + City |). + region |. + states)? ([^ Clan]+) (County | city |. Ethnic Autonomous Counties) | ((. +) city)? ([^ Clan]+) (Zone | flag)) $ ',
' \1\6\9 '); End If; return new_addr; End F_addr;

In this function, the key is actually a regular expression, the function body with a few if else to deal with a few special cases, not covering all the situation, just cover a few, this can be discussed later, directly to see the last else, here This is the most basic use of the regular:

NEW_ADDR: = Regexp_replace (Address, ' ^ ([^ Provincial]+) (province | (. Autonomous Regions | municipalities) ((. + City |). + region |. + states)? ([^ Clan]+) (County | city |. Ethnic Autonomous Counties) | ((. +) city)? ([^ Clan]+) (Zone | flag)) $ ',
' \1\6\9 ');
This built-in function, check the Oracle API, see the meaning is similar to guess, is a return to a new string to replace the original string function, here simply, the first argument address, is the source string,
The second is our regular pattern, the third is the return string, the third argument ' \1\6\9 ', if the familiar with the regular classmate, may feel déjà vu, which is to take the sub-expression, from left to right, left parenthesis of the sort,
is our sub-expression of the sort, take the first sixth Nineth sub-expression, intuitive to take out, is to take ([^ ethnic]+) and ([^ Clan]+) and (. +), you find the right one.

The analysis of this regular here, in fact, is very simple, the overall use of two brackets divided into two parts: Head and tail.
The first one: ^ ([^ Provincial]+) (province | (. Autonomous Region | city),
The second One: ((. + City |. + region |. + states)? ([^ Clan]+) (County | city |. Ethnic Autonomous Counties) | ((. +) city)? ([^ Clan]+) (Zone | flag)) $
To put it simply,
The first one is to match the provinces, autonomous regions and municipalities
The second one is to match the city and county level and the city level two,
\1 Nature is to take the name of the province, such as: Shandong Province Weifang Changle County, \1 will be taken: Shandong,
\6 is to take the county level name, for example: Shandong Province Weifang Changle County, \6 will take: Changle,
\9 is to take provinces and cities such administrative level of the name of the city, such as: Shandong Province Weifang Hanting District, \9 will take to Weifang
When the expression matches to \6, the return value of \9 will be an empty string, and when matched to \9, \6 will return an empty string, so this expression duly satisfies our needs,
Other situation, here first not table, is special deal with the next national Autonomous County, Autonomous prefecture, Xinjiang Ah and so on.

Students are welcome to discuss together in doubt.

Oracle Regular intercept String function