我们要找1个或多个非“/“字符，可以使用”[^/]+“。^在方括号中表示NOT。我们还需要移除可选的双引号所以需要使用[^/”]+。所以如果我们需要获取第3次出现的字符串：
SELECT REGEXP_SUBSTR(data, '[^/"]+', 1, 3) AS element3
FROM t1;
ELEMENT3
---------------------------------------------------------------------
GZ120804
BANANA
APPLE
3 rows selected.
SQL>
Example 4 : REGEXP_REPLACE
We need to take an initcap string and separate the words. The datalooks like this.
我们需要提取首字母大写的字符串并将其分离。原始数据如下：
DROP TABLE t1;
CREATE TABLE t1 (
data VARCHAR2(50)
);
INSERT INTO t1 VALUES ('SocialSecurityNumber');
INSERT INTO t1 VALUES ('HouseNumber');
COMMIT;
We need to find each uppercase character "[A-Z]". Wewant to keep that character we find, so we will make that pattern asub-expression "([A-Z])", allowing us to refer to it later. For eachmatch, we want to replace it with a space, plus the matching character. Thespace is pretty obvious, but we need to use "\1" to signify the textmatching the first sub expression. So we will replace the matching pattern witha space and itself, " \1". We don't want to replace the first letterof the string, so we will start at the second occurrence.
我们需要使用[A-Z]找到每个大写字符。我们需要保留找到的字符，所以我们使用一个子表达式([A-Z])，以便后续对其引用。对于每一个匹配，我们想使用一个空格替换，加上匹配到的字符。空格是相当明显的，但我们需要使用”\1”表示第一个子表达式匹配的文本。所以我们替换匹配模式使用一个空格和其自身，即”\1”。我们不想替换字符串的第一个字母，所以我们从第2个字符开始：
SELECT REGEXP_REPLACE(data, '([A-Z])', ' \1', 2) AS hyphen_text
FROM t1;
HYPHEN_TEXT
--------------------------------------------------------------------
Social Security Number
House Number
2 rows selected.
SQL>
Example 5 : REGEXP_INSTR
We have a specific pattern of digits (9 99:99:99) and we want toknow the location of the pattern in our data.
我们有一个指定数字模式（999:99:99）并且我们想知道模式在我们数据中所处位置。
DROP TABLE t1;
CREATE TABLE t1 (
data VARCHAR2(50)
);
INSERT INTO t1 VALUES ('1 01:01:01');
INSERT INTO t1 VALUES ('.2 02:02:02');
INSERT INTO t1 VALUES ('..3 03:03:03');
COMMIT;
We know we are looking for groups of numbers, so we can use"[0-9]" or "\d". We know the amount of digits in eachgroup, which we can indicate using the "{n}" operator, so we simplydescribe the pattern we are looking for.
我们知道我们正在找一组数字，所以使用"[0-9]"或"\d"。我们知道每一组数字的数量，所以可以使用{n}操作符，所以我们简单描述一下模式：
SELECT REGEXP_INSTR(data, '[0-9] [0-9]{2}:[0-9]{2}:[0-9]{2}') AS string_loc_1,
REGEXP_INSTR(data, '\d \d{2}:\d{2}:\d{2}') AS string_loc_2
FROM t1;
STRING_LOC_1 STRING_LOC_2
------------ ------------
1 1
2 2
3 3
3 rows selected.
SQL>
Example 6 : REGEXP_LIKE andREGEXP_SUBSTR
We have strings containing parentheses. We want to return the textwithin the parentheses for those rows that contain parentheses.
我们有包含在括号内的字符串。我们想只想返回括号内的字符串。
DROP TABLE t1;
CREATE TABLE t1 (
data VARCHAR2(50)
);
INSERT INTO t1 VALUES ('This is some text (with parentheses) in it.');
INSERT INTO t1 VALUES ('This text has no parentheses.');
INSERT INTO t1 VALUES ('This text has (parentheses too).');
COMMIT;
The basic pattern for text between parentheses is"\(.*\)". The "\" characters are escapes for theparentheses, making them literals. Without the escapes they would be assumed todefine a sub-expression. That pattern alone is fine to identify the rows of interestusing a REGEXP_LIKE operator,but it is not appropriate in a REGEXP_SUBSTR, as itwould return the parentheses also. To omit the