SQL Essay 1 substr and InStr

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.