For a great god, and now want to get some information, information content format as follows, & gt; Anhui province & gt; Hefei & gt; Feixi county & gt; Experimental primary school & gt; : in a class, the information is the first level in anhui province, hefei is the second level, feixi county is the third level, experimental primary school is 4, a class is the fifth, use between each level symbol & gt; Separated, and now I want to take 3 and 4 respectively, how to write a script? Note: per length is not the same level, such as the second possible input three words in hefei, hefei also some input two characters, with no city,

CodePudding user response:

The select regexp_substr (s, '[^ & gt;] + ', 1, 3), regexp_substr (s, '[^ & gt;] + ', 1, 4) from
(select '& gt; Anhui province & gt; Hefei & gt; Feixi county & gt; Experimental primary school & gt; A class 's from dual)

CodePudding user response:

 select substr (TAB, instr (TAB, '& gt; ', 1, 3) + 1, instr (TAB, '& gt; ', 1, 4-1 - instr (TAB, '& gt; ', 1, 3)) in the third grade, substr (TAB, instr (TAB, '& gt; ', 1, 4) + 1, instr (TAB, '& gt; ', 1, 5) - 1 - instr (TAB, '& gt; ', 1, 4)) 4 
The from (select '& gt; Anhui province & gt; Hefei & gt; Feixi county & gt; Experimental primary school & gt; A class 'TAB from dual); 

CodePudding user response:

Chengccy given query is correct, ORACLE's regular expressions can easily solve the problem,

REGEXP_SUBSTR has four parameters, the first parameter is the string to parse, the second parameter is the regular expression, here [^ & gt;] + does not contain the character "& gt;" Continuous string, the third argument which characters from the start, there are 1, are the string, and the fourth parameter is said which conform to the conditions of regular expressions,
Execute the following query can see effect,

The SELECT regexp_substr (' & gt; Anhui province & gt; Hefei & gt; Feixi county & gt; Experimental primary school & gt; A class ', '[^ & gt;] + ', 1, 1) FROM dual;
The SELECT regexp_substr (' & gt; Anhui province & gt; Hefei & gt; Feixi county & gt; Experimental primary school & gt; A class ', '[^ & gt;] + ', 1, 2) FROM dual;
The SELECT regexp_substr (' & gt; Anhui province & gt; Hefei & gt; Feixi county & gt; Experimental primary school & gt; A class ', '[^ & gt;] + ', 1, 3) FROM dual;
The SELECT regexp_substr (' & gt; Anhui province & gt; Hefei & gt; Feixi county & gt; Experimental primary school & gt; A class ', '[^ & gt;] + ', 1, 4) FROM dual;
The SELECT regexp_substr (' & gt; Anhui province & gt; Hefei & gt; Feixi county & gt; Experimental primary school & gt; A class ', '[^ & gt;] + ', 1, 5) FROM dual;