InStr and SUBSTR functions ____ functions

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

Its syntax is:

InStr (Sourcestring,deststring,start,appearposition). InStr (' Source string ', ' target string ', ' Start position ', ' first occurrence ')

Where sourcestring represents the source string;

Deststring represents a substring to look for in 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 optional, and defaults to 1;

If the value of start is a negative number, the representation is looked up from right to left, but the location data is still computed from left to right.

The return value is: The position of the string found.

For the InStr function, we often use this: to find the position of a specified substring from a string. For example:

Sql> Select InStr (' Yuechaotianyuechao ', ' ao ') position from dual;

POSITION

----------

6

Start a search from the 7th character

Sql> Select InStr (' Yuechaotianyuechao ', ' AO ', 7) position from dual;

POSITION

----------

17

Searches for the 2nd occurrence of a substring, starting with the 1th character

Sql> Select InStr (' Yuechaotianyuechao ', ' ao ', 1, 2) position from dual;

POSITION

----------

17

Note: 1. If the ' start position ' = 0 o'clock returns a result of 0,

2. There are only three parameters here, which means finding the first position to find the character (because ' the number appears ' defaults to 1),

When the ' starting position ' is not greater than the position of the first character you want to find, the returned value will be the position of the first character, and if the ' starting position ' is greater than the position of the first character to find, the returned value will be the position of the 2nd character, and so on ... (but also counting starting with the first character)

The use of the SUBSTR function to get a string that specifies the starting position and length of the string, by default from the start position to the ending substring.

substr (String, start_position, [length]) substr (' Target string ', start position, length)

Such as:

SUBSTR (' This are a test ', 6, 2) would return ' is '

SUBSTR (' This are a test ', 6) would return ' is a test '

substr (' techonthenet ', -3, 3) would return ' Net '

substr (' Techonthenet ',-6, 3) would return ' the ' Select substr (' Thisisatest ', -4, 2) value from dual

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

About the InStr () and substr () functions-

InStr a total of 4 parameters, the specific format is:

INSTR (Strings|express,strings[,m,[n]])

Searches for a specified character in a string, returning the location of the specified character;

strings|express the string being searched

Strings The string you want to search for

The start position of the M search, default is 1

n n times the position of the string you want to search for, the default is 1

1. The searched string can be a string, or it can be an expression

Such as:

QUOTE:

Sql> Select InStr (' I 2 Firefly ', ' I ') result from dual;

Result

----------

7

Sql> Select InStr (Initcap (' My 2 Firefly ') | | B ', ' Fi ') result from dual;

Result

----------

6

2. The string you want to search for can be either a character or a numeric character (you want to search for one or more string lengths)

Such as:

QUOTE:

Sql> Select InStr (' I 2 Firefly ', ' I ') result from dual;

Result

----------

7

Sql> Select InStr (' I 2 Firefly ', ' iref ') result from dual;

Result

----------

7

Sql> Select InStr (' I 2 Firefly ', 2) result from dual;

Result

----------

4

Sql> Select InStr (' My 2 Firefly ', "result" from dual;

Result

----------

0

3.M means to start the search from the first few characters

Such as:

QUOTE:

Sql> Select InStr (' My 2 Firefly ', ' F ', 1) result from dual;

Result

----------

6

Note: If m=0, return a result of 0

Select InStr (' I 2 Firefly ', ' F ', 0) result from dual;

Result

----------

0

Note: There are only three parameters, meaning to find the first position to find the character (since n defaults to 1),

When M is not greater than the position of the first character to find, the returned value will be the position of the first character, and if M is greater than the position of the first character to find, the returned value will be the position of the 2nd character, and so forth ...

Such as:

QUOTE:

Sql> Select InStr (' My 2 Firefly ', ' F ', 1) result from dual;

Result

----------

6

Sql> Select InStr (' My 2 Firefly ', ' F ', 3) result from dual;

Result

----------

6

Sql> Select InStr (' My 2 Firefly ', ' F ', 6) result from dual;

Result

----------

6

Sql> Select InStr (' My 2 Firefly ', ' F ', 7) result from dual;

Result

----------

10

Sql> Select InStr (' My 2 Firefly ', ' F ', ten) result from dual;

Result

----------

10

Returns 0 when M is greater than the maximum position to find the character

QUOTE:

Sql> Select InStr (' My 2 Firefly ', ' F ', one) result from dual;

Result

----------

0

Sql> Select InStr (' My 2 Firefly ', ' F ', #) result from dual;

Result

----------

0

And vice versa, if m<0, it means finding data from right to left

QUOTE:

Sql> Select InStr (' My 2 Firefly ', ' F ', -1) result from dual;

Result

----------

10

Sql> Select InStr (' My 2 Firefly ', ' F ', -2) result from dual;

Result

----------

10

Sql> Select InStr (' My 2 Firefly ', ' F ', -4) result from dual;

Result

----------

6

Sql> Select InStr (' My 2 Firefly ', ' F ', -40) result from dual;

Result

----------

0

4.N means to find the nth character

Such as:

QUOTE:

Sql> Select InStr (' My 2 Firefly ', ' F ', 1,1) result from dual;

Result

----------

6

Sql> Select InStr (' My 2 Firefly ', ' F ', 1,2) result from dual;

Result

----------

10

Sql> Select InStr (' My 2 Firefly ', ' F ', 1,3) result from dual;

Result

----------

0

Returns 0 when n is greater than the maximum number of occurrences in the lookup source containing the string to find.

Such as:

QUOTE:

Sql> Select InStr (' My 2 Firefly ', ' F ', 1,4) result from dual;

Result

----------

0

Sql> Select InStr (' My 2 Firefly ', ' F ', 1,40) result from dual;

Result

----------

0

When M<0, represents a right-to-left lookup, such as:

QUOTE:

Sql> Select InStr (' My 2 Firefly ', ' F ', -10,1) result from dual;

Result

----------

0

Sql> Select InStr (' My 2 Firefly ', ' F ', -8,1) result from dual;

Result

----------

0

Sql> Select InStr (' My 2 Firefly ', ' F ', -3,1) result from dual;

Result

----------

10

Note: When M is not greater than the position of the first character to find, the returned value will be the position of the first character, and if M is greater than the position of the first character to find, the returned value will be the position of the 2nd character, and so on ...

Such as:

QUOTE:

Sql> Select InStr (' My 2 Firefly ', ' F ', -4,1) result from dual;

Result

----------

6

Equivalent to:

Sql> Select InStr (' My 2 Firefly ', ' F ', -1,2) result from dual;

Result

----------

6

Note also: If the string you are looking for is NULL, return null

Such as:

Sql> Select InStr (' I 2 Firefly ', null,-1,2) result from dual;

Result

----------

Sql> Select InStr (' I 2 Firefly ', null) result from dual;

Result

----------

Sql> Select InStr (' I 2 Firefly ', null,1) result from dual;

Result

----------

QUOTE:

================================================

String intercept function substr (Strings|express,m,[n])

Strings|express an intercepted string or string expression

M to intercept from the first m character

N after interception the string length is n

1. Truncated as String or string expression

QUOTE:

Sql> Select substr (Upper (' ABCDE '), 1,2) from dual;

SUBSTR (UPPER (' ABCDE '), 1,2)

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

Ab

Sql> Select substr (' ABCDE ', 1,2) from dual;

SUBSTR (' ABCDE ', 1,2)

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

Ab

2. If m<0, it means to intercept from right to left

Here if n>0, if n>=|m|, intercept string length |m| (from right to left),

If n<|m|, intercept string length n (intercept from right to left)

Such as:

QUOTE:

Sql> Select substr (' ABCDE ', -4,2) from dual;

SUBSTR (' ABCDE ', -4,2)

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

Bc

Sql> Select substr (' ABCDE ', -4,1) from dual;

SUBSTR (' ABCDE ', -4,1)

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

B

Sql> Select substr (' ABCDE ', -4,4) from dual;

SUBSTR (' ABCDE ', -4,4)

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

Bcde

Sql> Select substr (' ABCDE ', -4,5) from dual;

SUBSTR (' ABCDE ', -4,5)

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

Bcde

Sql> Select substr (' ABCDE ', -4,50) from dual;

SUBSTR (' ABCDE ', -4,50)

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

Bcde

If the m<0,n=0 result is null:

QUOTE:

Sql> Select substr (' ABCDE ', -4,0) from dual;

SUBSTR (' ABCDE ', -4,0)

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

Sql> Select substr (' ABCDE ', -5,0) from dual;

SUBSTR (' ABCDE ', -5,0)

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

If the m<0,n<0 result is null:

QUOTE:

Sql> Select substr (' ABCDE ', -2,0) from dual;

SUBSTR (' ABCDE ', -2,0)

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

Sql> Select substr (' ABCDE ', -2,-1) from dual;

SUBSTR (' ABCDE ', -2,-1)

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

Sql> Select substr (' ABCDE ', -2,-4) from dual;

SUBSTR (' ABCDE ', -2,-4)

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

Sql> Select substr (' ABCDE ', -3,-4) from dual;

SUBSTR (' ABCDE ', -3,-4)

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

3. If n<=0, the result is 0:

QUOTE:

Sql> Select substr (' ABCDE ', 1,-4) from dual;

SUBSTR (' ABCDE ', 1,-4)

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

Sql> Select substr (' ABCDE ', 1,-2) from dual;

SUBSTR (' ABCDE ', 1,-2)

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

Sql> Select substr (' ABCDE ', 1,0) from dual;

SUBSTR (' ABCDE ', 1,0)

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

Sql> Select substr (' ABCDE ', 1,-2) from dual;

SUBSTR (' ABCDE ', 1,-2)

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

4.m,n If one is null, the result is null

QUOTE:

Sql> Select substr (' ABCDE ', 1,null) from dual;

SUBSTR (' ABCDE ', 1,null)

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

Sql> Select substr (' ABCDE ', null,null) from dual;

SUBSTR (' ABCDE ', null,null)

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

Sql> Select substr (' ABCDE ', null,1) from dual;

SUBSTR (' ABCDE ', null,1)

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

5. Do not use the N parameter:

In N=null, the result is null.

N>=0, the result is to intercept from N characters to the last

QUOTE:

Sql> Select substr (' ABCDE ', null) from dual;

SUBSTR (' ABCDE ', NULL)

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

Sql> Select substr (' ABCDE ', 0) from dual;

SUBSTR (' ABCDE ', 0)

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

Abcde

Sql> Select substr (' ABCDE ', 1) from dual;

SUBSTR (' ABCDE ', 1)

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

Abcde

Sql> Select substr (' ABCDE ', 2) from dual;

SUBSTR (' ABCDE ', 2)

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

Bcde

N<0, if the length of the |n|<= is intercepted,

The result is to intercept |n| characters from right to left

QUOTE:

Sql> Select substr (' ABCDE ', -2) from dual;

SUBSTR (' ABCDE ',-2)

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

De

Sql> Select substr (' ABCDE ', -3) from dual;

SUBSTR (' ABCDE ',-3)

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

Cde

Sql> Select substr (' ABCDE ', -5) from dual;

SUBSTR (' ABCDE ',-5)

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

Abcde

If the length of the |n|> string is truncated, the result is null

QUOTE:

Sql> Select substr (' ABCDE ', -6) from dual;

SUBSTR (' ABCDE ',-6)

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

Sql> Select substr (' ABCDE ', -60) from dual;