Shulou(Shulou.com)06/01 Report--

1. The format of the instr () function (commonly known as the character search function)

Format 1: instr (string1, string2) / / instr (source string, target string)

Format 2: instr (string1, string2 [, start_position [, nth_appearance]]) / / instr (source string, target string, starting position, matching sequence number)

Parsing: the value of string2 is to be found in string1, which is retrieved in string1 from the value given by start_position (that is, location), and the nth _ appearance (several times) string2 appears.

Note: in Oracle/PLSQL, the instr function returns the position of the string to be intercepted in the source string. Retrieve it only once, that is, from the beginning of the character to the end of the character.

2. Examples

Format one

Select instr ('helloworld','l') from dual;-return result: 3 location where "l" appears for the first time by default select instr (' helloworld','lo') from dual;-return result: 4, that is, "lo" appears simultaneously (continuously), "l" position select instr ('helloworld','wo') from dual;-return result: 6, that is, where "w" begins to appear.

Format two

Select instr ('helloworld','l',2,2) from dual;-returns the result: 4 that is, starting at position 2 (e) of "helloworld", find the location of the second occurrence of "l" select instr (' helloworld','l',3,2) from dual -- return result: 4 that is to say: start at position 3 (l) of "helloworld", find the location of the second occurrence of "l" select instr ('helloworld','l',4,2) from dual;-- return the result: 9, that is, start at position 4 (l) of "helloworld", find the location of the second occurrence of "l" select instr (' helloworld','l',-1,1) from dual -- return result: 9 that is to say: start at the penultimate position 1 (d) of "helloworld", go back to find the location of the first occurrence of "l" select instr ('helloworld','l',-2,2) from dual;-- return the result: 4: start at the penultimate (l) position of "helloworld", go back to find the location of the second occurrence of "l" select instr (' helloworld','l',2,3) from dual -- return result: 9 that is to say: start at position 2 (e) of "helloworld", find the location of the third occurrence of "l" select instr ('helloworld','l',-2,3) from dual;-- return the result: 3, that is, start at the penultimate position of "helloworld" and go back to find the location of the third occurrence of "l"

Note: the fuzzy query like in MySQL has the same query effect as the instr () function in Oracle, as shown below:

MySQL: select * from tableName where name like'% helloworld%';Oracle:select * from tableName where instr (name,'helloworld') > 0;-- these two statements have the same effect

3. Screenshot of an example

1 、

2 、

3 、

4 、

5 、

6 、

7 、

8 、

9 、

The above is the whole content of this article, I hope it will be helpful to your study, and I also hope that you will support it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.