regexp_like( source_string ,pattern )
regexp_like(source_string ,pattern ,match_parameter )
-
source_string
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_string
, Oracle convertspattern
to the datatype ofsource_string
. For a listing of the operators you can specify inpattern
, please refer to Appendix C, " Oracle Regular Expression Support". -
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 wildcard 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.
-
pattern 是正则表达式
match_parameter 匹配参数
‘i’不区分大小写
‘c’区分大小写
例如:
select A.CODE AS arrivalStationCode,
A.NAME AS arrivalStationName,
A.RAIL_BUREAU_NAME AS railReceiveBureau,
B.RECEIVING_CODE AS receiveUnitCode,
B.RECEIVING_NAME AS receiveUnitName,
A.PROVINCE_NAME AS province,
B.RAILWAY_NAME AS railwayName
from B_PORT_STATION_INFO A, B_STATION_RECEIVING_STORAGE B
where A.CODE = B.STATION_CODE(+) and regexp_like(A.MNEMONIC,'[N]','i')
但是如果在写sql时使用占位符
regexp_like(A.MNEMONIC,'[?]','i')
可能不会正确执行,程序可能将占位符看做正则符号。