Introduction of SQL function substr

Time:2021-4-18

Interpretation of substr (string, 1,3) function: take 3-word long string from left to right in string.

The results were: str

From right to left?

There should be another function to provide such a function!

In fact, it’s the same function from right to left, just with different parameters.

Interpretation of substr (string, – 1,3) function: take the right one in the string and start to take three.

The results were as follows: G

substr(string,start,length)

String – the specified string to intercept.
Start – required, specifies where to start the string. A positive number – starts at the specified position in the string, a negative number – starts at the specified position at the end of the string, and 0 – starts at the first character in the string.
Length – optional. It specifies the length of the string to be intercepted. By default, it returns all characters before the end of the value of the character expression.

For example: select substr (‘abcdefg ‘, 3,4) from dual; the result is cdef
Select substr (‘abcdefg ‘, – 3,4) from dual; result EFG

Note: the first position in the string is always 1. The results of the following two SQL queries are the same:

For example: select substr (‘abcdefg ‘, 0,3) from dual; the result is ABC

Select substr (‘abcdefg ‘, 1,3) from dual; the result is ABC

Different from substring in Java class:

Substring (int beginindex, int endindex): starts at the specified beginindex and ends at the specified endindex-1. The first position in the string is always 0. Endindex, optional. By default, it returns the character from the specified index to the end of the string.

For example: “unhappy”. Substring (2) returns “happy”

           “hamburger”.substring(3,8) returns “burge”

The above is the whole content of this article, I hope you can like it.