Implementing query results in Oracle Sort InStr functions by in-in conditions

Oracle, you can use the INSTR function to judge a string to determine whether it contains the specified character.

Its syntax is:
INSTR (string, substring, position, occurrence)
which

String: Represents the source string;

SUBSTRING: Represents a substring that you want to look up from a source string;

Position: Represents the start position of the lookup, which is optional, defaults to 1, and the default lookup order is left to right. When the starting position is a negative number, start looking from the right.

Occurrence: The representative wants to find out the first occurrence of the substring from the source character, this parameter is optional, default is 1;
If the value of the position is a negative number, the representation is looked up from right to left.
The return value is: The position of the string found.

For the INSTR function, we often use this: to find the position of a specified substring from a string.

For example:

The display result of the SELECT Instr (' Hello Word ', ' O ',-1, 1) ' String ' from Dual is

Instring
————
8

The format of the InStr method is:
INSTR (source string, target string, start position, match ordinal)

For example:

INSTR (' CORPORATE FLOOR ', ' or ', 3, 2), the source string is ' CORPORATE FLOOR ', the target string is ' OR ', the starting position is 3, and the position of the 2nd match is taken.

The default lookup order is left to right. When the starting position is a negative number, start looking from the right.

So the display result of the Select INSTR (' CORPORATE FLOOR ', ' or ',-1, 1) ' instring ' from dual is

Instring
——————
14

Special Usage:

Special usage in the final analysis is nothing special, we see above the "source string", "target string" are written dead, in fact it can also be a field, see the following example:

Select ID, name from users where InStr (' 101914, 104703 ', id) > 0;

it is equivalent to

Select ID, name from users where id = 101914 or id = 104703;

Application:

The use of InStr is due to the fact that I entered the ID of multiple people in the foreground and wanted to return the information of these people. For example: "Wang Wu, male, computer science students, Xiao Liang, male, art students." ”

Front, receive the input ID, use "," separate, in the background I do not want to do it separately to handle, can use the InStr statement, and then use for loop connection results.

There's another one, probably less common:

Select ID, name from users where InStr (ID, ' a ') > 0;

Equivalent to

Select ID, name from users where id like '%101% '

There has not been a year-on-year test for the performance of this use of InStr. Sort: select A.resattributeid,a.attributeenname,b.resattributeid,a.resclassenname from M_resattribute a,m_ Resattribute_jsrmw_bak b
where A.resclassenname=b.resclassenname and A.attributeenname=b.attributeenname
and B.resattributeid in (155977,172505,155967,155952,155951,278943,155976,155963,227385,172503,279664)
Order by InStr (' 155977,172505,155967,155952,155951,278943,155976,155963,227385,172503,279664 ', B.resattributeid)