Oracle intercepts a portion of a string in a range of strings

Implement IndexOf and LastIndexOf features in Oracle SQL, substr and InStr Usage Blog Category: Oraclepl/sql instrsubstrlastindexofindexoforacle. Implementing IndexOf and LASTINDEXOF capabilities in Oracle SQL Oracle, SQL, INDEXOF, lastIndexOf today, there is a problem that requires a string that is formatted as 1_2_3 to be split into 1_3 strings, Use SQL to update the database. In Oracel, this function can be implemented with the SUBSTR and InStr functions. The third parameter of substr is 1 o'clock, and the IndexOf function is implemented. When the third parameter of SUBSTR is-1, the LastIndexOf function is implemented. The final SQL statement is as follows: Update ebms_data_table t set t.ref_name = substr (t.name, 0, INSTR (t.name, ' _ ', 1, 1)) | | substr (T.name, INSTR (T.name, ' _ ',-1, 1) +1); (b) Give us an example: select I1.name, InStr (I1.name, 'Division', 1) + 1 as company appeared position, InStr (I1.name, ' branch ', 1) as branch appeared position, substr (I1.name, InStr (I1.name, ' Division ') , 1) + 1, ((InStr (I1.name, ' branch ', 1))-(InStr (I1.name, ' Division ', 1) + 1))) as SubName from Insurance_company1                             I1 where i1.name like '% Co., Ltd% Branch ' and I1.upcustomer as '% Company Limited ' query results are as follows: Full name Division appears position SubName 1 days Safety Insurance shares limited publicDivisionYilisubsidiaries11 13 Ili 2 China land Property Insurance shares limited publicDivisionXinjiangsubsidiaries15 17 Xinjiang 3 China Ping An property insurance shares limited publicDivisionWususubsidiaries15 17 Wusu 4 China Ping An property insurance shares limited publicDivisionXinjiangsubsidiaries15 17 Xinjiang 5 China Ping An Life Insurance Co., ltdDivisionXinjiangsubsidiaries15 17 Xinjiang 6 Chinese People's Property insurance shares limited publicDivisionAksusubsidiaries15 20 Aksu 7 Chinese People's Property insurance shares limited publicDivisionAltaysubsidiaries15 20 Altay 8 Chinese People's Property insurance shares limited publicDivisionChangji Hui Autonomous Prefecturesubsidiaries15 22 The SubName behind the Changji Hui Autonomous Prefecture is the name of my successful interception, which is the field of the various branches in the back of the full name that I want to intercept, in order to get a different field and then analyze it. This is an integrated use of substr and instr. SUBSTR (stirng1,strat,length); intercept function; parse: String1 is the string you want to intercept, Strat is where you want to start the intercept, and length is the number of strings you want to intercept. InStr (String1,string2,end); is a retrieval function, parsing: String1 is the string you want to retrieve, string2 is the string you want to find in string1, end means 1 to retrieve from the beginning of the string,- 1 represents the beginning of the string in the end of the search. 　　The specific return value if string1 is zero length instr returns 0 string1 is null instr returns null string2 is zero length InStr returns the value of Start string2 to null InStr returns Null string2 cannot find InStr return 0 found in string1 string2 InStr return found position start value > Len (string1) value InStr return 0; When InStr finds the location where we want to retrieve the field, it returns the location data, we can use the SUBSTR function to intercept, and length is the difference between the numbers we have retrieved.

Select
I1.fdisplayname,
InStr (I1.fdisplayname, ' thing ', "+1"),
InStr (I1.fdisplayname, ' industry ', +1) as industry,
SUBSTR (I1.fdisplayname,
InStr (I1.fdisplayname, ' thing ', 1, 1) +1,
((InStr (I1.fdisplayname, ' Karma ', ")")-(InStr (I1.fdisplayname, ' matter ', 1, 1) +1))) As SubName
From SHENZHENJM1222. B I1

In three steps:

1.

Select
I1.fdisplayname,
InStr (I1.fdisplayname, ' thing ', "+1"),
InStr (I1.fdisplayname, ' industry ', +1) as industry
From SHENZHENJM1222. B I1

Part II:

Select
SUBSTR (I1.fdisplayname,
InStr (I1.fdisplayname, ' Things ', ") +1, ((InStr (I1.fdisplayname, ' Karma ',") +1)-(InStr (I1.fdisplayname, ' thing ', +1)))
As SubName from SHENZHENJM1222. B I1

If you want to change the boundary value, just + the number behind

Oracle intercepts a portion of a string in a range of strings