Oracle substr () and SUBSTRB () ____oracle

has been used substr () to intercept a part of the string, although know that there are SUBSTRB (), but did not use, also do not understand where to use them, today just test the two functions, do some summary:

Let's start by introducing the two functions

Function Name Description

SUBSTR (C1, N1) from the starting position specified in the string to get the following string

SUBSTRB (C1, N1)

SUBSTR (C1, N1, N2) A string that obtains the specified number of characters from the starting position specified in the string

SUBSTRB (c1, n1,n2) gets a string of the specified number of bytes from the start position specified in the string

The "argument" C1 is a string, N1 is the starting position, and N2 is the number of characters/sections

From the above you can see that the difference between substr () and SUBSTRB () is that substr () is the number of characters to get the string, SUBSTRB () is the number of bytes to get the string.

For example:

1 Get string "It's a nice day," the 3rd character begins with a string

Select substr (' The weather is very good today ', 3) from dual;

The result of the output is: The weather is very good

2 Get String "Today is a nice day," the 3rd byte begins with a string

Select SUBSTRB (' The weather is very good today ', 3) from dual;

The result of the output is: It's a nice day.

3 Get the string "Today is a nice day", the 3rd character begins with a 2 character string

Select substr (' The weather is very good today ', 3,2) from dual;

The result of the output is: weather

4 Get the string "Today is a nice day", the 3rd byte begins with a 2-byte string

Select SUBSTRB (' The weather is very good today ', 3,2) from dual;

The output is: Day

Note: When the start position is set to a negative number, it is retrieved from the end of the string.

Similar to these two functions are:
Length and LENGTHB calculation function

Select Length (' Weather ') from dual;

Output: 2

Select LENGTHB (' Hello ') from dual;

Output: 4