In Oracle, The instr function is sorted according to the conditions in the in statement.

 

In Oracle, you can use the instr function to determine whether a string contains specified characters.

Its syntax is:
Instr (string, substring, position, occurrence)
Where

String: indicates the source string;

Substring: The substring to be searched from the source string;

Position: indicates the start position of the search. this parameter is optional. The default value is 1. The default search order is left to right. When the start position is negative, search from the right.

Occurrence: specifies the number of substrings that appear from the source character list. This parameter is optional. The default value is 1;
If the position value is negative, it indicates searching from right to left.
Return Value: the position of the searched string.

For instr functions, we often use this method to find the position of the specified substring from a string.

For example:

Select instr ('Hello word', 'O',-1, 1) "string" from dual:

Instring
----
8

 

 

The format of the instr method is:
Instr (source string, target string, start position, matching serial number)

For example:

In instr ('upgrade floor ', 'or', 3, 2), the source string is 'upgrade floor', the target string is 'or', And the start position is 3, take the location of 2nd matching items.

The default search order is left to right. When the start position is negative, search from the right.

Therefore, the result of select instr ('upgrade floor ',' or ',-1, 1) "instring" from dual is:

Instring
------
14

Special usage:

In the end, there is nothing special about the special usage. The "Source string" and "target string" we saw above are all dead. In fact, they can also be fields. Let's look at the example below:

Select ID, name from users where instr ('1970 3', ID)> 0;

It is equivalent

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

Purpose:

The reason why instr is used is that I continuously enter the IDs of multiple personnel at the front-end and want to return the information of these personnel. For example, "Wang Wu, male, computer student; Xiao Liang, male, and art student ."

The front-end receives input IDs separated by commas (,). In the backend, I can use the instr statement and connect them with the for loop statement.

Another one may not be frequently used:

Select ID, name from users where instr (ID, '20140901')> 0;

Equivalent

Select ID, name from users where Id like '% 100'

No year-on-year tests have been conducted on the Performance 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 ('1996, 155967, 155952,155951, 278943,155976, 155963,227385, 172503,279664', B. resattributeid)