Hàm REGEXP_SUBSTR – Oracle/PLSQL


Hàm REGEXP_SUBSTR – Oracle/PLSQL là một trong các hàm trong danh sách được cung cấp sẵn cho người sử dụng khi thao tác với hệ quản trị cơ sở dữ liệu Oracle.
Oracle-PLSQL-function tranning-1-min


Hàm REGEXP_SUBSTR – Oracle/PLSQL – Chức năng

The Oracle/PLSQL REGEXP_SUBSTR function is an extension of the SUBSTR function. This function, introduced in Oracle 10g, will allow you to extract a substring from a string using regular expression pattern matching.

Hàm REGEXP_SUBSTR – Oracle/PLSQL – Cú pháp

Cú pháp Hàm REGEXP_SUBSTR trong Oracle/PLSQL như sau :

REGEXP_SUBSTR( string, pattern [, start_position [, nth_appearance [, match_parameter [, sub_expression ] ] ] ] )
Tham số
string
The string to search. It can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
pattern

The regular expression matching information. It can be a combination of the following:

Value Description
^ Matches the beginning of a string. If used with a match_parameter of ‘m’, it matches the start of a line anywhere within expression.
$ Matches the end of a string. If used with a match_parameter of ‘m’, it matches the end of a line anywhere within expression.
* Matches zero or more occurrences.
+ Matches one or more occurrences.
? Matches zero or one occurrence.
. Matches any character except NULL.
| Used like an "OR" to specify more than one alternative.
[ ] Used to specify a matching list where you are trying to match any one of the characters in the list.
[^ ] Used to specify a nonmatching list where you are trying to match any character except for the ones in the list.
( ) Used to group expressions as a subexpression.
{m} Matches m times.
{m,} Matches at least m times.
{m,n} Matches at least m times, but no more than n times.
\n n is a number between 1 and 9. Matches the nth subexpression found within ( ) before encountering \n.
[..] Matches one collation element that can be more than one character.
[::] Matches character classes.
[==] Matches equivalence classes.
\d Matches a digit character.
\D Matches a nondigit character.
\w Matches a word character.
\W Matches a nonword character.
\s Matches a whitespace character.
\S matches a non-whitespace character.
\A Matches the beginning of a string or matches at the end of a string before a newline character.
\Z Matches at the end of a string.
*? Matches the preceding pattern zero or more occurrences.
+? Matches the preceding pattern one or more occurrences.
?? Matches the preceding pattern zero or one occurrence.
{n}? Matches the preceding pattern n times.
{n,}? Matches the preceding pattern at least n times.
{n,m}? Matches the preceding pattern at least n times, but not more than m times.
start_position
Optional. It is the position in string where the search will start. If omitted, it defaults to 1 which is the first position in the string.
nth_appearance
Optional. It is the nth appearance of pattern in string. If omitted, it defaults to 1 which is the first appearance of pattern in string.
match_parameter

Optional. It allows you to modify the matching behavior for the REGEXP_SUBSTR function. It can be a combination of the following:

Value Description
‘c’ Perform case-sensitive matching.
‘i’ Perform case-insensitive matching.
‘n’ Allows the period character (.) to match the newline character. By default, the period is a wildcard.
‘m’ expression is assumed to have multiple lines, where ^ is the start of a line and $ is the end of a line, regardless of the position of those characters in expression. By default, expression is assumed to be a single line.
‘x’ Whitespace characters are ignored. By default, whitespace characters are matched like any other character.
subexpression
Optional. This is used when pattern has subexpressions and you wish to indicate which subexpression in pattern is the target. It is an integervalue from 0 to 9 indicating the subexpression to match on in pattern.

Trong quá trình sử dụng hàm REGEXP_SUBSTR trong Oracle/PLSQL hãy nhớ :

Hàm REGEXP_SUBSTR – Oracle/PLSQL – Chú ý

  • If there are conflicting values provided for match_parameter, the REGEXP_SUBSTR function will use the last value.
  • If you omit the match_behavior parameter, the REGEXP_SUBSTR function will use the NLS_SORT parameter to determine if it should use a case-sensitive search, it will assume that string is a single line, and assume the period character to match any character (not the newline character).
  • If the REGEXP_SUBSTR function does not find any occurrence of pattern, it will return NULL.
  • See also the SUBSTR function.

Hàm REGEXP_SUBSTR – Oracle/PLSQL – Phạm vi

Hàm REGEXP_SUBSTR có thể sẵn sàng sử dụng trong Oracle/PLSQL với các phiên bản Oracle như sau:

Oracle 12c, Oracle 11g, Oracle 10g

Hàm REGEXP_SUBSTR – Oracle/PLSQL – Ví dụ

Dưới đây là các ví dụ minh họa cách sử dụng hàm REGEXP_SUBSTR trong Oracle/PLSQL như sau:

Example – Match on Words

Let’s start by extracting the first word from a string.

For example:

SELECT REGEXP_SUBSTR ('TechOnTheNet is a great resource', '(\S*)(\s)')
FROM dual;

Result: 'TechOnTheNet '

This example will return ‘TechOnTheNet ‘ because it will extract all non-whitespace characters as specified by (\S*) and then the first whitespace character as specified by (\s). The result will include both the first word as well as the space after the word.

If you didn’t want to include the space in the result, we could modify our example as follows:

SELECT REGEXP_SUBSTR ('TechOnTheNet is a great resource', '(\S*)')
FROM dual;

Result: 'TechOnTheNet'

This example would return ‘TechOnTheNet’ with no space at the end.

If we wanted to find the second word in the string, we could modify our function as follows:

SELECT REGEXP_SUBSTR ('TechOnTheNet is a great resource', '(\S*)(\s)', 1, 2)
FROM dual;

Result: 'is '

This example would return ‘is ‘ with a space at the end of the string.

If we wanted to find the third word in the string, we could modify our function as follows:

SELECT REGEXP_SUBSTR ('TechOnTheNet is a great resource', '(\S*)(\s)', 1, 3)
FROM dual;

Result: 'a '

This example would return ‘a ‘ with a space at the end of the string.


Tham khảo các hàm built-in trong Oracle/PLSQL khác

STT Tên hàm Phạm vi Chi tiết
1 ABS Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
2 ACOS Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
3 ADD_MONTHS Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
4 ASCII Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
5 ASCIISTR Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i xem
6 ASIN Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
7 ATAN Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
8 ATAN2 Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
9 AVG Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
10 BFILENAME Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
11 BIN_TO_NUM Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i xem
12 BITAND Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
13 CARDINALITY Oracle 12c, Oracle 11g, Oracle 10g xem
14 CASE Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i xem
15 CEIL Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
16 CHR Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
17 COALESCE Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i xem
18 CONCAT Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
19 Concat with || Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
20 CORR Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
21 COS Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
22 COSH Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
23 COVAR_POP Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
24 COVAR_SAMP Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
25 CUME_DIST Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i xem
26 CURRENT_DATE Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i xem
27 CURRENT_TIMESTAMP Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i xem
28 DBTIMEZONE Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i xem
29 DECODE Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i xem
30 DECOMPOSE Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i xem
31 DENSE_RANK Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i xem
32 DUMP Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
33 EMPTY_BLOB Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
34 EMPTY_CLOB Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
35 EXP Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
36 EXTRACT Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i xem
37 FIRST_VALUE Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i xem
38 FLOOR Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
39 FROM_TZ Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i xem
40 GREATEST Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
41 GROUP_ID Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i xem
42 HEXTORAW Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
43 INITCAP Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
44 INSTR Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
45 INSTR2 Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
46 INSTR4 Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
47 INSTRB Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
48 INSTRC Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
49 LAG Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
50 LAST_DAY Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
51 LAST_VALUE Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i xem
52 LEAD Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
53 LEAST Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
54 LENGTH Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
55 LENGTH2 Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
56 LENGTH4 Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
57 LENGTHB Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
58 LENGTHC Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
59 LISTAGG Oracle 12c, Oracle 11g Release 2 xem
60 LN Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
61 LOCALTIMESTAMP Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
62 LOG Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
63 LOWER Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
64 LPAD Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
65 LTRIM Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
66 MAX Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
67 MEDIAN Oracle 12c, Oracle 11g, Oracle 10g xem
68 MIN Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
69 MONTHS_BETWEEN Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
70 NANVL Oracle 12c, Oracle 11g, Oracle 10g xem
71 NEW_TIME Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i xem
72 NEXT_DAY Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i xem
73 NTH_VALUE Oracle 12c, Oracle 11g Release 2 xem
74 NULLIF Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i xem
75 NUMTODSINTERVAL Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
76 NUMTOYMINTERVAL Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
77 NVL Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
78 NVL2 Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
79 POWER Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
80 RANK Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i xem
81 REGEXP_COUNT Oracle 12c, Oracle 11g xem
82 REGEXP_INSTR Oracle 12c, Oracle 11g, Oracle 10g xem
83 REGEXP_REPLACE Oracle 12c, Oracle 11g, Oracle 10g xem
84 REGEXP_SUBSTR Oracle 12c, Oracle 11g, Oracle 10g xem
85 REPLACE Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
86 ROUND (numbers) Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
87 ROWNUM Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
88 RPAD Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
89 RTRIM Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
90 SESSIONTIMEZONE Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i xem
91 SIGN Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
92 SIN Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
93 SINH Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
94 SQRT Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
95 STDDEV Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
96 SUBSTR Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
97 SUM Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
98 SYSDATE Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
99 SYSTIMESTAMP Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i xem
100 TAN Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
101 TANH Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
102 TO_CHAR Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
103 TO_CLOB Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i xem
104 TO_DATE Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
105 TO_DSINTERVAL Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i xem
106 TO_LOB Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
107 TO_MULTI_BYTE Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
108 TO_NCLOB Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i xem
109 TO_NUMBER Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
110 TO_SINGLE_BYTE Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
111 TO_TIMESTAMP Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i xem
112 TO_TIMESTAMP_TZ Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i xem
113 TO_YMINTERVAL Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i xem
114 TRANSLATE Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
115 TRIM Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
116 TRUNC (dates) Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
117 TRUNC (numbers) Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
118 UID Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
119 UPPER Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
120 USER Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
121 USERENV Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
122 VAR_POP Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
123 VAR_SAMP Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
124 VARIANCE Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem
125 VSIZE Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i xem

Blog được lập ra với mục đích để chia sẻ đề thi tới toàn bộ học sinh, sinh viên ở Việt Nam. Trong quá trình đăng bài không thể tránh được những sai sót, Nếu các bạn phát hiện ra sai sót có thể comment ở bên dưới hoặc có đề hay muốn chia sẻ tới tất cả mọi người có thể gửi thông tin vào email [email protected]


Mít&Béo


Phản hồi

Phản hồi