Hàm REGEXP_COUNT – Oracle/PLSQL


Hàm REGEXP_COUNT – 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-2-min


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

The Oracle/PLSQL REGEXP_COUNT function counts the number of times that a pattern occurs in a string. This function, introduced in Oracle 11g, will allow you to count the number of times a substring occurs in a string using regular expression pattern matching.

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

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

REGEXP_COUNT( string, pattern [, start_position [, atch_parameter ] ] )
Tham số
string
The string to search. string 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.
match_parameter

Optional. It allows you to modify the matching behavior for the REGEXP_COUNT 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.

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

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

  • If there are conflicting values provided for match_parameter, the REGEXP_COUNT function will use the last value.
  • If you omit the match_behavior parameter, the REGEXP_COUNT 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_COUNT function does not find any occurrence of pattern, it will return 0.

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

Hàm REGEXP_COUNT 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

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

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

Example – Match on Single Character

Let’s start by looking at the simplest case. Let’s count the number of times the character ‘t’ appears in a string.

For example:

SELECT REGEXP_COUNT ('TechOnTheNet is a great resource', 't')
FROM dual;

Result: 2

This example will return 2 because it is counting the number of occurrences of ‘t’ in the string. Since we did not specify a match_parameter value, the REGEXP_COUNT function will perform a case-sensitive search which means that the ‘T’ characters will not be included in the count.

If we wanted to include both ‘t’ and ‘T’ in our results and perform a case-insensitive search, we could modify our query as follows:

SELECT REGEXP_COUNT ('TechOnTheNet is a great resource', 't', 1, 'i')
FROM dual;

Result: 4

Now because we have provide a start_position of 1 and a match_parameter of ‘i’, the query will return 4 as the result. This time, both ‘t’ and ‘T’ values would be included in the count.

If we wanted to count the number of ‘t’ in a column, we could try something like this:

SELECT REGEXP_COUNT (last_name, 't', 1, 'i') AS total
FROM contacts;

This would count the number of ‘t’ or ‘T’ values in the last_name field from the contacts table.


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