OCP-047 REGEXP_INSTR()

91. View the Exhibit and examine the data in the LOCATIONS table.
Evaluate the following SQL statement:
SELECT street_address
FROM locations WHERE REGEXP_INSTR(street_address,'[^[:alpha:]]') = 1;----- -查询street_address已数字开头的地址信息而非字母开头

Which statement is true regarding the output of this SQL statement?

A. It would display all the street addresses that do not have a substring
'alpha'.
B. It would display all the street addresses where the first character is
a special character.
C. It would display all the street addresses where the first character is
a letter of the alphabet.
D. It would display all the street addresses where the first character is
not a letter of the alphabet.

Answer: D

解题说明：

[....]： 匹配字符list

[^ ... ]：不匹配字符list

[:class:]：字符类

常用的标准字符类如下：

alnum    字母和数字

alpha     字母

blank     空白，包括空格、制表符等

digit      数字

lower     小写字母

space   空白，包括空格、制表符、竖向制表符、换行、回车，注意与 blank 类的区别

upper    大写字母

xdigit    十六进制数字字符

举例：

REGEXP_INSTR

Syntax

Description of the illustration regexp_instr.gif

Purpose

REGEXP_INSTR extends the functionality of the INSTR function by letting you search a string for a regular expression pattern. The function evaluates strings using characters as defined by the input character set. It returns an integer indicating the beginning or ending position of the matched substring, depending on the value of the return_option argument. If no match is found, the function returns 0.

This function complies with the POSIX regular expression standard and the Unicode Regular Expression Guidelines. For more information, please refer toAppendix C, "Oracle Regular Expression Support".

• source_char is a character expression that serves as the search value. It is commonly a character column and can be of any of the datatypes CHAR,VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

• pattern is the regular expression. It is usually a text literal and can be of any of the datatypes CHAR, VARCHAR2, NCHAR, or NVARCHAR2. It can contain up to 512 bytes. If the datatype of pattern is different from the datatype of source_char, Oracle Database converts pattern to the datatype of source_char. For a listing of the operators you can specify in pattern, please refer to Appendix C, "Oracle Regular Expression Support".

• position is a positive integer indicating the character of source_char where Oracle should begin the search. The default is 1, meaning that Oracle begins the search at the first character of source_char.

• occurrence is a positive integer indicating which occurrence of pattern in source_char Oracle should search for. The default is 1, meaning that Oracle searches for the first occurrence of pattern.

• return_option lets you specify what Oracle should return in relation to the occurrence:

  • If you specify 0, then Oracle returns the position of the first character of the occurrence. This is the default.

  • If you specify 1, then Oracle returns the position of the character following the occurrence.

• match_parameter is a text literal that lets you change the default matching behavior of the function. You can specify one or more of the following values for match_parameter:

  • 'i' specifies case-insensitive matching.

  • 'c' specifies case-sensitive matching.

  • 'n' allows the period (.), which is the match-any-character character, to match the newline character. If you omit this parameter, the period does not match the newline character.

  • 'm' treats the source string as multiple lines. Oracle interprets ^ and $ as the start and end, respectively, of any line anywhere in the source string, rather than only at the start or end of the entire source string. If you omit this parameter, Oracle treats the source string as a single line.

  • 'x' ignores whitespace characters. By default, whitespace characters match themselves.

  If you specify multiple contradictory values, Oracle uses the last value. For example, if you specify 'ic', then Oracle uses case-sensitive matching. If you specify a character other than those shown above, then Oracle returns an error.

  If you omit match_parameter, then:

  • The default case sensitivity is determined by the value of the NLS_SORT parameter.

  • A period (.) does not match the newline character.

  • The source string is treated as a single line.