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.

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:

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

Result: 2

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

Result: 4

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

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

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