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?
'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
Description of the illustration regexp_instr.gif
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 datatypesCHAR
,VARCHAR2
,NCHAR
,NVARCHAR2
,CLOB
, orNCLOB
. -
pattern
is the regular expression. It is usually a text literal and can be of any of the datatypesCHAR
,VARCHAR2
,NCHAR
, orNVARCHAR2
. It can contain up to 512 bytes. If the datatype ofpattern
is different from the datatype ofsource_char
, Oracle Database convertspattern
to the datatype ofsource_char
. For a listing of the operators you can specify inpattern
, please refer to Appendix C, "Oracle Regular Expression Support". -
position
is a positive integer indicating the character ofsource_char
where Oracle should begin the search. The default is 1, meaning that Oracle begins the search at the first character ofsource_char
. -
occurrence
is a positive integer indicating which occurrence ofpattern
insource_char
Oracle should search for. The default is 1, meaning that Oracle searches for the first occurrence ofpattern
. -
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 formatch_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.
-