What is the method for extracting a substring in Oracle using the function substr()?

2 years ago
Liam
1 minute

In Oracle, the SUBSTR function can be used to extract a portion of a string. The syntax of the SUBSTR function is as follows:

SUBSTR(string, start_position, [length])

Among them:

  1. “String is the string to be truncated.”
  2. start_position is the position from where the extraction should begin, it can be a positive integer indicating the starting character of the string, or a negative integer indicating the starting character from the end of the string.
  3. The length parameter is optional, indicating the number of characters to be extracted. If this parameter is omitted, all characters from the start_position to the end of the string will be extracted.

Here are some examples of using the SUBSTR function:

-- 截取字符串的前5个字符
SELECT SUBSTR('Hello, World!', 1, 5) FROM dual;
-- 输出：'Hello'

-- 截取字符串的后6个字符
SELECT SUBSTR('Hello, World!', -6) FROM dual;
-- 输出：'World!'

-- 截取字符串的第2个字符开始的所有字符
SELECT SUBSTR('Hello, World!', 2) FROM dual;
-- 输出：'ello, World!'

It is important to note that in Oracle, string indexes start from 1, not from 0.

bannerAds