SQL Essay 1 substr and InStr

Select Riskcode,outriskcode,riskname,suppliercode, (select min (inputdate) from Fccont where Innercontno in (select Innercontno from Fcpol where Riskcode=t.riskcode and Biztype= ' "and Corporation in (' 1137000003 ', ' 1137000004 '))) from Mr ISK T where Riskname in (' Allianz Anconfore Insurance B ', ' Allianz Super-split insurance dividend type ') and substr (suppliercode,0,4) in (' 1060 ', ' 1052 ', ' 1050 ', ' 1031 ', ' 1037 ', ' 1030 ', ' 1015 ', ' 1069 ', ' 1096 ', ' 1049 ') Order by InStr (' Allianz Anconfore both insurance B, Allianz Super-split insurance dividend type ', riskname)

SUBSTR (suppliercode,0,4) in (' 1060 ', ' 1052 ', ' 1050 ', ' 1031 ', ' 1037 ', ' 1030 ', ' 1015 ', ' 1069 ', ' 1096 ', ' 1049 ')

Substr

SUBSTR () function
1. Usefulness: Returns a substring from the given character expression or Memo field.
2. Syntax format: SUBSTR (cexpression,nstartposition [, ncharactersreturned])
Where cexpression specifies the character expression or Memo field from which to return the string;
Nstartposition is used to specify the position of the returned string in a character expression or Memo field.
The ncharactersreturned is used to specify the number of characters returned, by default returning all characters before the value of the character expression ends.

Example: STORE ' abcdefghijlkm ' to mystring
SUBSTR (mystring, 1,5) displays "ABCDE" 1 characters that are truncated from the first character, including the first character
SUBSTR (mystring, 6) show "FGHIJKLM"
SUBSTR (mystring,-2) shows "km" the rightmost character is-1, the rightmost left character is-2, then the default is left to take all the remaining characters
SUBSTR (mystrng,-4) shows "jlkm"

where Riskname in (' Allianz Anconfore ' B ', ' Allianz Super-share insurance dividend type ') Order by InStr (' Allianz Anconfore B, Allianz Super-share insurance dividend type ', riskname)

The

can use the InStr function to judge a string to determine whether it contains the specified character.  

finds the specified character in a string and returns the location of the specified character found by the search.  
Syntax:  
InStr (Sourcestring,deststring,start,appearposition)  
InStr (' Source string ', ' target string ', ' start position ', ' first occurrence ')  
where sourcestring represents the source string,  
Deststring represents the substring to be looked up from the source string;  
start represents the starting position of the lookup, which is optional, The default is 1; , and the
Appearposition represents the number of occurrences of the deststring from the source character, which is optional, and the default is 1 
If the value of start is negative, the lookup is from right to left. However, the location data is still calculated from left to right.   The
return value is: The location of the found string.  
for the InStr function, we often use this: find the location of the specified substring from a string. For example:  
sql> Select InStr (' abcdefgh ', ' de ') position from dual; 
position 
---------- 
   4 
starting from 1 to calculate D Row IV so return 4 

Sql>select InStr (' Abcdefghbc ', ' BC ', 3) position from dual ;  
position 
---------- 
  9 
Start with the 3rd character 3rd character is C, so look up the string after 3 to find the BC, return 9  

---------------------------
Starting with the 1th character, find the location of the 2nd occurrence of a substring
Sql> Select InStr (' Qinyinglianqin ', ' Qin ', 1, 2) position from dual;
POSITION
----------
12

SQL Essay 1 substr and InStr