Common functions of Oracle InStr () and substr () functions

from:http://1055592535.iteye.com/blog/1676235

In Oracle

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

Finds the specified character in a string, returning the position of the specified character that was found.

Grammar:
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 and defaults to 1;

The Appearposition representative wants to find out the first occurrence of the deststring from the source character, which is also optional and defaults to 1

If the value of start is negative, the lookup is made from right to left, but 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, the D row is the four, so return 4.

Sql>select InStr (' Abcdefghbc ', ' BC ', 3) position from dual;

POSITION
----------
9
The 3rd character starting from the 3rd character is C, so find the BC after the 3 string, 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

----------------------------------------------------------------------

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.

3. 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"

Common functions of Oracle InStr () and substr () functions