Chức năng biểu thức chính quy và mô tả toán tử
Regexp_substr [________ 17, ________ 8 [, ________ 29 [, ________ 30 [,
2]]]]]]mysql> SELECT REGEXP_LIKE['Michael!', '.*']; +-------------------------------+ | REGEXP_LIKE['Michael!', '.*'] | +-------------------------------+ | 1 | +-------------------------------+ mysql> SELECT REGEXP_LIKE['new*\n*line', 'new\\*.\\*line']; +----------------------------------------------+ | REGEXP_LIKE['new*\n*line', 'new\\*.\\*line'] | +----------------------------------------------+ | 0 | +----------------------------------------------+ mysql> SELECT REGEXP_LIKE['a', '^[a-d]']; +----------------------------+ | REGEXP_LIKE['a', '^[a-d]'] | +----------------------------+ | 1 | +----------------------------+
Trả về chuỗi con của chuỗi
7 phù hợp với biểu thức chính quy được chỉ định bởi mẫumysql> SELECT REGEXP_INSTR['dog cat dog', 'dog']; +------------------------------------+ | REGEXP_INSTR['dog cat dog', 'dog'] | +------------------------------------+ | 1 | +------------------------------------+ mysql> SELECT REGEXP_INSTR['dog cat dog', 'dog', 2]; +---------------------------------------+ | REGEXP_INSTR['dog cat dog', 'dog', 2] | +---------------------------------------+ | 9 | +---------------------------------------+ mysql> SELECT REGEXP_INSTR['aa aaa aaaa', 'a{2}']; +-------------------------------------+ | REGEXP_INSTR['aa aaa aaaa', 'a{2}'] | +-------------------------------------+ | 1 | +-------------------------------------+ mysql> SELECT REGEXP_INSTR['aa aaa aaaa', 'a{4}']; +-------------------------------------+ | REGEXP_INSTR['aa aaa aaaa', 'a{4}'] | +-------------------------------------+ | 8 | +-------------------------------------+
pat
,
1 nếu không có khớp. Nếumysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE']; +---------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE'] | +---------------------------------------+ | 1 | +---------------------------------------+ mysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs]; +------------------------------------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs] | +------------------------------------------------------------------+ | 0 | +------------------------------------------------------------------+
7 hoặcmysql> SELECT REGEXP_INSTR['dog cat dog', 'dog']; +------------------------------------+ | REGEXP_INSTR['dog cat dog', 'dog'] | +------------------------------------+ | 1 | +------------------------------------+ mysql> SELECT REGEXP_INSTR['dog cat dog', 'dog', 2]; +---------------------------------------+ | REGEXP_INSTR['dog cat dog', 'dog', 2] | +---------------------------------------+ | 9 | +---------------------------------------+ mysql> SELECT REGEXP_INSTR['aa aaa aaaa', 'a{2}']; +-------------------------------------+ | REGEXP_INSTR['aa aaa aaaa', 'a{2}'] | +-------------------------------------+ | 1 | +-------------------------------------+ mysql> SELECT REGEXP_INSTR['aa aaa aaaa', 'a{4}']; +-------------------------------------+ | REGEXP_INSTR['aa aaa aaaa', 'a{4}'] | +-------------------------------------+ | 8 | +-------------------------------------+
pat
là
1, giá trị trả về làmysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE']; +---------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE'] | +---------------------------------------+ | 1 | +---------------------------------------+ mysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs]; +------------------------------------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs] | +------------------------------------------------------------------+ | 0 | +------------------------------------------------------------------+
1.mysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE']; +---------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE'] | +---------------------------------------+ | 1 | +---------------------------------------+ mysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs]; +------------------------------------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs] | +------------------------------------------------------------------+ | 0 | +------------------------------------------------------------------+
16 lấy các đối số tùy chọn này:mysql> SELECT REGEXP_INSTR['dog cat dog', 'dog']; +------------------------------------+ | REGEXP_INSTR['dog cat dog', 'dog'] | +------------------------------------+ | 1 | +------------------------------------+ mysql> SELECT REGEXP_INSTR['dog cat dog', 'dog', 2]; +---------------------------------------+ | REGEXP_INSTR['dog cat dog', 'dog', 2] | +---------------------------------------+ | 9 | +---------------------------------------+ mysql> SELECT REGEXP_INSTR['aa aaa aaaa', 'a{2}']; +-------------------------------------+ | REGEXP_INSTR['aa aaa aaaa', 'a{2}'] | +-------------------------------------+ | 1 | +-------------------------------------+ mysql> SELECT REGEXP_INSTR['aa aaa aaaa', 'a{4}']; +-------------------------------------+ | REGEXP_INSTR['aa aaa aaaa', 'a{4}'] | +-------------------------------------+ | 8 | +-------------------------------------+
9: Vị trí trongmysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE']; +---------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE'] | +---------------------------------------+ | 1 | +---------------------------------------+ mysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs]; +------------------------------------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs] | +------------------------------------------------------------------+ | 0 | +------------------------------------------------------------------+
7 để bắt đầu tìm kiếm. Nếu bị bỏ qua, mặc định là 1.mysql> SELECT REGEXP_INSTR['dog cat dog', 'dog']; +------------------------------------+ | REGEXP_INSTR['dog cat dog', 'dog'] | +------------------------------------+ | 1 | +------------------------------------+ mysql> SELECT REGEXP_INSTR['dog cat dog', 'dog', 2]; +---------------------------------------+ | REGEXP_INSTR['dog cat dog', 'dog', 2] | +---------------------------------------+ | 9 | +---------------------------------------+ mysql> SELECT REGEXP_INSTR['aa aaa aaaa', 'a{2}']; +-------------------------------------+ | REGEXP_INSTR['aa aaa aaaa', 'a{2}'] | +-------------------------------------+ | 1 | +-------------------------------------+ mysql> SELECT REGEXP_INSTR['aa aaa aaaa', 'a{4}']; +-------------------------------------+ | REGEXP_INSTR['aa aaa aaaa', 'a{4}'] | +-------------------------------------+ | 8 | +-------------------------------------+
0: Sự xuất hiện của một trận đấu để tìm kiếm. Nếu bị bỏ qua, mặc định là 1.mysql> SELECT REGEXP_LIKE['Michael!', '.*']; +-------------------------------+ | REGEXP_LIKE['Michael!', '.*'] | +-------------------------------+ | 1 | +-------------------------------+ mysql> SELECT REGEXP_LIKE['new*\n*line', 'new\\*.\\*line']; +----------------------------------------------+ | REGEXP_LIKE['new*\n*line', 'new\\*.\\*line'] | +----------------------------------------------+ | 0 | +----------------------------------------------+ mysql> SELECT REGEXP_LIKE['a', '^[a-d]']; +----------------------------+ | REGEXP_LIKE['a', '^[a-d]'] | +----------------------------+ | 1 | +----------------------------+
Để biết thêm thông tin về cách kết hợp phù hợp, hãy xem mô tả cho
5.mysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE']; +---------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE'] | +---------------------------------------+ | 1 | +---------------------------------------+ mysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs]; +------------------------------------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs] | +------------------------------------------------------------------+ | 0 | +------------------------------------------------------------------+
mysql> SELECT 'Michael!' REGEXP '.*'; +------------------------+ | 'Michael!' REGEXP '.*' | +------------------------+ | 1 | +------------------------+ mysql> SELECT 'new*\n*line' REGEXP 'new\\*.\\*line'; +---------------------------------------+ | 'new*\n*line' REGEXP 'new\\*.\\*line' | +---------------------------------------+ | 0 | +---------------------------------------+ mysql> SELECT 'a' REGEXP '^[a-d]'; +---------------------+ | 'a' REGEXP '^[a-d]' | +---------------------+ | 1 | +---------------------+
Regexp_substr [________ 17, ________ 8 [, ________ 29 [, ________ 30 [,
2]]]]]]mysql> SELECT REGEXP_LIKE['Michael!', '.*']; +-------------------------------+ | REGEXP_LIKE['Michael!', '.*'] | +-------------------------------+ | 1 | +-------------------------------+ mysql> SELECT REGEXP_LIKE['new*\n*line', 'new\\*.\\*line']; +----------------------------------------------+ | REGEXP_LIKE['new*\n*line', 'new\\*.\\*line'] | +----------------------------------------------+ | 0 | +----------------------------------------------+ mysql> SELECT REGEXP_LIKE['a', '^[a-d]']; +----------------------------+ | REGEXP_LIKE['a', '^[a-d]'] | +----------------------------+ | 1 | +----------------------------+
Trả về chuỗi con của chuỗi
7 phù hợp với biểu thức chính quy được chỉ định bởi mẫumysql> SELECT REGEXP_INSTR['dog cat dog', 'dog']; +------------------------------------+ | REGEXP_INSTR['dog cat dog', 'dog'] | +------------------------------------+ | 1 | +------------------------------------+ mysql> SELECT REGEXP_INSTR['dog cat dog', 'dog', 2]; +---------------------------------------+ | REGEXP_INSTR['dog cat dog', 'dog', 2] | +---------------------------------------+ | 9 | +---------------------------------------+ mysql> SELECT REGEXP_INSTR['aa aaa aaaa', 'a{2}']; +-------------------------------------+ | REGEXP_INSTR['aa aaa aaaa', 'a{2}'] | +-------------------------------------+ | 1 | +-------------------------------------+ mysql> SELECT REGEXP_INSTR['aa aaa aaaa', 'a{4}']; +-------------------------------------+ | REGEXP_INSTR['aa aaa aaaa', 'a{4}'] | +-------------------------------------+ | 8 | +-------------------------------------+
pat
,
1 nếu không có khớp. Nếumysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE']; +---------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE'] | +---------------------------------------+ | 1 | +---------------------------------------+ mysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs]; +------------------------------------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs] | +------------------------------------------------------------------+ | 0 | +------------------------------------------------------------------+
7 hoặcmysql> SELECT REGEXP_INSTR['dog cat dog', 'dog']; +------------------------------------+ | REGEXP_INSTR['dog cat dog', 'dog'] | +------------------------------------+ | 1 | +------------------------------------+ mysql> SELECT REGEXP_INSTR['dog cat dog', 'dog', 2]; +---------------------------------------+ | REGEXP_INSTR['dog cat dog', 'dog', 2] | +---------------------------------------+ | 9 | +---------------------------------------+ mysql> SELECT REGEXP_INSTR['aa aaa aaaa', 'a{2}']; +-------------------------------------+ | REGEXP_INSTR['aa aaa aaaa', 'a{2}'] | +-------------------------------------+ | 1 | +-------------------------------------+ mysql> SELECT REGEXP_INSTR['aa aaa aaaa', 'a{4}']; +-------------------------------------+ | REGEXP_INSTR['aa aaa aaaa', 'a{4}'] | +-------------------------------------+ | 8 | +-------------------------------------+
pat
là
1, giá trị trả về làmysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE']; +---------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE'] | +---------------------------------------+ | 1 | +---------------------------------------+ mysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs]; +------------------------------------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs] | +------------------------------------------------------------------+ | 0 | +------------------------------------------------------------------+
1.mysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE']; +---------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE'] | +---------------------------------------+ | 1 | +---------------------------------------+ mysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs]; +------------------------------------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs] | +------------------------------------------------------------------+ | 0 | +------------------------------------------------------------------+
16 lấy các đối số tùy chọn này:mysql> SELECT REGEXP_INSTR['dog cat dog', 'dog']; +------------------------------------+ | REGEXP_INSTR['dog cat dog', 'dog'] | +------------------------------------+ | 1 | +------------------------------------+ mysql> SELECT REGEXP_INSTR['dog cat dog', 'dog', 2]; +---------------------------------------+ | REGEXP_INSTR['dog cat dog', 'dog', 2] | +---------------------------------------+ | 9 | +---------------------------------------+ mysql> SELECT REGEXP_INSTR['aa aaa aaaa', 'a{2}']; +-------------------------------------+ | REGEXP_INSTR['aa aaa aaaa', 'a{2}'] | +-------------------------------------+ | 1 | +-------------------------------------+ mysql> SELECT REGEXP_INSTR['aa aaa aaaa', 'a{4}']; +-------------------------------------+ | REGEXP_INSTR['aa aaa aaaa', 'a{4}'] | +-------------------------------------+ | 8 | +-------------------------------------+
9: Vị trí trongmysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE']; +---------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE'] | +---------------------------------------+ | 1 | +---------------------------------------+ mysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs]; +------------------------------------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs] | +------------------------------------------------------------------+ | 0 | +------------------------------------------------------------------+
7 để bắt đầu tìm kiếm. Nếu bị bỏ qua, mặc định là 1.mysql> SELECT REGEXP_INSTR['dog cat dog', 'dog']; +------------------------------------+ | REGEXP_INSTR['dog cat dog', 'dog'] | +------------------------------------+ | 1 | +------------------------------------+ mysql> SELECT REGEXP_INSTR['dog cat dog', 'dog', 2]; +---------------------------------------+ | REGEXP_INSTR['dog cat dog', 'dog', 2] | +---------------------------------------+ | 9 | +---------------------------------------+ mysql> SELECT REGEXP_INSTR['aa aaa aaaa', 'a{2}']; +-------------------------------------+ | REGEXP_INSTR['aa aaa aaaa', 'a{2}'] | +-------------------------------------+ | 1 | +-------------------------------------+ mysql> SELECT REGEXP_INSTR['aa aaa aaaa', 'a{4}']; +-------------------------------------+ | REGEXP_INSTR['aa aaa aaaa', 'a{4}'] | +-------------------------------------+ | 8 | +-------------------------------------+
0: Sự xuất hiện của một trận đấu để tìm kiếm. Nếu bị bỏ qua, mặc định là 1.mysql> SELECT REGEXP_LIKE['Michael!', '.*']; +-------------------------------+ | REGEXP_LIKE['Michael!', '.*'] | +-------------------------------+ | 1 | +-------------------------------+ mysql> SELECT REGEXP_LIKE['new*\n*line', 'new\\*.\\*line']; +----------------------------------------------+ | REGEXP_LIKE['new*\n*line', 'new\\*.\\*line'] | +----------------------------------------------+ | 0 | +----------------------------------------------+ mysql> SELECT REGEXP_LIKE['a', '^[a-d]']; +----------------------------+ | REGEXP_LIKE['a', '^[a-d]'] | +----------------------------+ | 1 | +----------------------------+
expr
Không Regexppat
,expr
không giốngpat
2: Một chuỗi chỉ định cách thực hiện khớp. Ý nghĩa được mô tả chomysql> SELECT REGEXP_LIKE['Michael!', '.*']; +-------------------------------+ | REGEXP_LIKE['Michael!', '.*'] | +-------------------------------+ | 1 | +-------------------------------+ mysql> SELECT REGEXP_LIKE['new*\n*line', 'new\\*.\\*line']; +----------------------------------------------+ | REGEXP_LIKE['new*\n*line', 'new\\*.\\*line'] | +----------------------------------------------+ | 0 | +----------------------------------------------+ mysql> SELECT REGEXP_LIKE['a', '^[a-d]']; +----------------------------+ | REGEXP_LIKE['a', '^[a-d]'] | +----------------------------+ | 1 | +----------------------------+
5.mysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE']; +---------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE'] | +---------------------------------------+ | 1 | +---------------------------------------+ mysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs]; +------------------------------------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs] | +------------------------------------------------------------------+ | 0 | +------------------------------------------------------------------+
Để biết thêm thông tin về cách kết hợp phù hợp, hãy xem mô tả cho
5.mysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE']; +---------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE'] | +---------------------------------------+ | 1 | +---------------------------------------+ mysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs]; +------------------------------------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs] | +------------------------------------------------------------------+ | 0 | +------------------------------------------------------------------+
mysql> SELECT REGEXP_INSTR['dog cat dog', 'dog']; +------------------------------------+ | REGEXP_INSTR['dog cat dog', 'dog'] | +------------------------------------+ | 1 | +------------------------------------+ mysql> SELECT REGEXP_INSTR['dog cat dog', 'dog', 2]; +---------------------------------------+ | REGEXP_INSTR['dog cat dog', 'dog', 2] | +---------------------------------------+ | 9 | +---------------------------------------+ mysql> SELECT REGEXP_INSTR['aa aaa aaaa', 'a{2}']; +-------------------------------------+ | REGEXP_INSTR['aa aaa aaaa', 'a{2}'] | +-------------------------------------+ | 1 | +-------------------------------------+ mysql> SELECT REGEXP_INSTR['aa aaa aaaa', 'a{4}']; +-------------------------------------+ | REGEXP_INSTR['aa aaa aaaa', 'a{4}'] | +-------------------------------------+ | 8 | +-------------------------------------+
Regexp_substr [________ 17, ________ 8 [, ________ 29 [, ________ 30 [,
2]]]]]]mysql> SELECT REGEXP_LIKE['Michael!', '.*']; +-------------------------------+ | REGEXP_LIKE['Michael!', '.*'] | +-------------------------------+ | 1 | +-------------------------------+ mysql> SELECT REGEXP_LIKE['new*\n*line', 'new\\*.\\*line']; +----------------------------------------------+ | REGEXP_LIKE['new*\n*line', 'new\\*.\\*line'] | +----------------------------------------------+ | 0 | +----------------------------------------------+ mysql> SELECT REGEXP_LIKE['a', '^[a-d]']; +----------------------------+ | REGEXP_LIKE['a', '^[a-d]'] | +----------------------------+ | 1 | +----------------------------+
9: Vị trí trongmysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE']; +---------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE'] | +---------------------------------------+ | 1 | +---------------------------------------+ mysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs]; +------------------------------------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs] | +------------------------------------------------------------------+ | 0 | +------------------------------------------------------------------+
7 để bắt đầu tìm kiếm. Nếu bị bỏ qua, mặc định là 1.mysql> SELECT REGEXP_INSTR['dog cat dog', 'dog']; +------------------------------------+ | REGEXP_INSTR['dog cat dog', 'dog'] | +------------------------------------+ | 1 | +------------------------------------+ mysql> SELECT REGEXP_INSTR['dog cat dog', 'dog', 2]; +---------------------------------------+ | REGEXP_INSTR['dog cat dog', 'dog', 2] | +---------------------------------------+ | 9 | +---------------------------------------+ mysql> SELECT REGEXP_INSTR['aa aaa aaaa', 'a{2}']; +-------------------------------------+ | REGEXP_INSTR['aa aaa aaaa', 'a{2}'] | +-------------------------------------+ | 1 | +-------------------------------------+ mysql> SELECT REGEXP_INSTR['aa aaa aaaa', 'a{4}']; +-------------------------------------+ | REGEXP_INSTR['aa aaa aaaa', 'a{4}'] | +-------------------------------------+ | 8 | +-------------------------------------+
0: Sự xuất hiện của một trận đấu để tìm kiếm. Nếu bị bỏ qua, mặc định là 1.mysql> SELECT REGEXP_LIKE['Michael!', '.*']; +-------------------------------+ | REGEXP_LIKE['Michael!', '.*'] | +-------------------------------+ | 1 | +-------------------------------+ mysql> SELECT REGEXP_LIKE['new*\n*line', 'new\\*.\\*line']; +----------------------------------------------+ | REGEXP_LIKE['new*\n*line', 'new\\*.\\*line'] | +----------------------------------------------+ | 0 | +----------------------------------------------+ mysql> SELECT REGEXP_LIKE['a', '^[a-d]']; +----------------------------+ | REGEXP_LIKE['a', '^[a-d]'] | +----------------------------+ | 1 | +----------------------------+
expr
Không Regexppat
,expr
không giốngpat
Điều này giống như
1 regexpmysql> SELECT REGEXP_INSTR['dog cat dog', 'dog']; +------------------------------------+ | REGEXP_INSTR['dog cat dog', 'dog'] | +------------------------------------+ | 1 | +------------------------------------+ mysql> SELECT REGEXP_INSTR['dog cat dog', 'dog', 2]; +---------------------------------------+ | REGEXP_INSTR['dog cat dog', 'dog', 2] | +---------------------------------------+ | 9 | +---------------------------------------+ mysql> SELECT REGEXP_INSTR['aa aaa aaaa', 'a{2}']; +-------------------------------------+ | REGEXP_INSTR['aa aaa aaaa', 'a{2}'] | +-------------------------------------+ | 1 | +-------------------------------------+ mysql> SELECT REGEXP_INSTR['aa aaa aaaa', 'a{4}']; +-------------------------------------+ | REGEXP_INSTR['aa aaa aaaa', 'a{4}'] | +-------------------------------------+ | 8 | +-------------------------------------+
pat
].expr
Regexppat
,expr
r Likepat
Trả về 1 nếu chuỗi
7 khớp với biểu thức chính quy được chỉ định bởi mẫumysql> SELECT REGEXP_INSTR['dog cat dog', 'dog']; +------------------------------------+ | REGEXP_INSTR['dog cat dog', 'dog'] | +------------------------------------+ | 1 | +------------------------------------+ mysql> SELECT REGEXP_INSTR['dog cat dog', 'dog', 2]; +---------------------------------------+ | REGEXP_INSTR['dog cat dog', 'dog', 2] | +---------------------------------------+ | 9 | +---------------------------------------+ mysql> SELECT REGEXP_INSTR['aa aaa aaaa', 'a{2}']; +-------------------------------------+ | REGEXP_INSTR['aa aaa aaaa', 'a{2}'] | +-------------------------------------+ | 1 | +-------------------------------------+ mysql> SELECT REGEXP_INSTR['aa aaa aaaa', 'a{4}']; +-------------------------------------+ | REGEXP_INSTR['aa aaa aaaa', 'a{4}'] | +-------------------------------------+ | 8 | +-------------------------------------+
pat
, 0 nếu không. Nếu
7 hoặcmysql> SELECT REGEXP_INSTR['dog cat dog', 'dog']; +------------------------------------+ | REGEXP_INSTR['dog cat dog', 'dog'] | +------------------------------------+ | 1 | +------------------------------------+ mysql> SELECT REGEXP_INSTR['dog cat dog', 'dog', 2]; +---------------------------------------+ | REGEXP_INSTR['dog cat dog', 'dog', 2] | +---------------------------------------+ | 9 | +---------------------------------------+ mysql> SELECT REGEXP_INSTR['aa aaa aaaa', 'a{2}']; +-------------------------------------+ | REGEXP_INSTR['aa aaa aaaa', 'a{2}'] | +-------------------------------------+ | 1 | +-------------------------------------+ mysql> SELECT REGEXP_INSTR['aa aaa aaaa', 'a{4}']; +-------------------------------------+ | REGEXP_INSTR['aa aaa aaaa', 'a{4}'] | +-------------------------------------+ | 8 | +-------------------------------------+
pat
là
1, giá trị trả về làmysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE']; +---------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE'] | +---------------------------------------+ | 1 | +---------------------------------------+ mysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs]; +------------------------------------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs] | +------------------------------------------------------------------+ | 0 | +------------------------------------------------------------------+
1.mysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE']; +---------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE'] | +---------------------------------------+ | 1 | +---------------------------------------+ mysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs]; +------------------------------------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs] | +------------------------------------------------------------------+ | 0 | +------------------------------------------------------------------+
3 vàmysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE']; +---------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE'] | +---------------------------------------+ | 1 | +---------------------------------------+ mysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs]; +------------------------------------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs] | +------------------------------------------------------------------+ | 0 | +------------------------------------------------------------------+
4 là từ đồng nghĩa vớimysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE']; +---------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE'] | +---------------------------------------+ | 1 | +---------------------------------------+ mysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs]; +------------------------------------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs] | +------------------------------------------------------------------+ | 0 | +------------------------------------------------------------------+
5.mysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE']; +---------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE'] | +---------------------------------------+ | 1 | +---------------------------------------+ mysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs]; +------------------------------------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs] | +------------------------------------------------------------------+ | 0 | +------------------------------------------------------------------+
Regexp_instr [________ 17, ________ 8 [, ________ 29 [, ________ 30 [, ________ 31 [,
2]]]]]]mysql> SELECT REGEXP_LIKE['Michael!', '.*']; +-------------------------------+ | REGEXP_LIKE['Michael!', '.*'] | +-------------------------------+ | 1 | +-------------------------------+ mysql> SELECT REGEXP_LIKE['new*\n*line', 'new\\*.\\*line']; +----------------------------------------------+ | REGEXP_LIKE['new*\n*line', 'new\\*.\\*line'] | +----------------------------------------------+ | 0 | +----------------------------------------------+ mysql> SELECT REGEXP_LIKE['a', '^[a-d]']; +----------------------------+ | REGEXP_LIKE['a', '^[a-d]'] | +----------------------------+ | 1 | +----------------------------+
Trả về chỉ số bắt đầu của chuỗi con của chuỗi
7 phù hợp với biểu thức chính quy được chỉ định bởi mẫumysql> SELECT REGEXP_INSTR['dog cat dog', 'dog']; +------------------------------------+ | REGEXP_INSTR['dog cat dog', 'dog'] | +------------------------------------+ | 1 | +------------------------------------+ mysql> SELECT REGEXP_INSTR['dog cat dog', 'dog', 2]; +---------------------------------------+ | REGEXP_INSTR['dog cat dog', 'dog', 2] | +---------------------------------------+ | 9 | +---------------------------------------+ mysql> SELECT REGEXP_INSTR['aa aaa aaaa', 'a{2}']; +-------------------------------------+ | REGEXP_INSTR['aa aaa aaaa', 'a{2}'] | +-------------------------------------+ | 1 | +-------------------------------------+ mysql> SELECT REGEXP_INSTR['aa aaa aaaa', 'a{4}']; +-------------------------------------+ | REGEXP_INSTR['aa aaa aaaa', 'a{4}'] | +-------------------------------------+ | 8 | +-------------------------------------+
pat
, 0 nếu không có khớp. Nếu
7 hoặcmysql> SELECT REGEXP_INSTR['dog cat dog', 'dog']; +------------------------------------+ | REGEXP_INSTR['dog cat dog', 'dog'] | +------------------------------------+ | 1 | +------------------------------------+ mysql> SELECT REGEXP_INSTR['dog cat dog', 'dog', 2]; +---------------------------------------+ | REGEXP_INSTR['dog cat dog', 'dog', 2] | +---------------------------------------+ | 9 | +---------------------------------------+ mysql> SELECT REGEXP_INSTR['aa aaa aaaa', 'a{2}']; +-------------------------------------+ | REGEXP_INSTR['aa aaa aaaa', 'a{2}'] | +-------------------------------------+ | 1 | +-------------------------------------+ mysql> SELECT REGEXP_INSTR['aa aaa aaaa', 'a{4}']; +-------------------------------------+ | REGEXP_INSTR['aa aaa aaaa', 'a{4}'] | +-------------------------------------+ | 8 | +-------------------------------------+
pat
là
1, giá trị trả về làmysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE']; +---------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE'] | +---------------------------------------+ | 1 | +---------------------------------------+ mysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs]; +------------------------------------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs] | +------------------------------------------------------------------+ | 0 | +------------------------------------------------------------------+
1. Chỉ số ký tự bắt đầu ở 1.mysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE']; +---------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE'] | +---------------------------------------+ | 1 | +---------------------------------------+ mysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs]; +------------------------------------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs] | +------------------------------------------------------------------+ | 0 | +------------------------------------------------------------------+
9 lấy các đối số tùy chọn này:mysql> SELECT REGEXP_LIKE['Michael!', '.*']; +-------------------------------+ | REGEXP_LIKE['Michael!', '.*'] | +-------------------------------+ | 1 | +-------------------------------+ mysql> SELECT REGEXP_LIKE['new*\n*line', 'new\\*.\\*line']; +----------------------------------------------+ | REGEXP_LIKE['new*\n*line', 'new\\*.\\*line'] | +----------------------------------------------+ | 0 | +----------------------------------------------+ mysql> SELECT REGEXP_LIKE['a', '^[a-d]']; +----------------------------+ | REGEXP_LIKE['a', '^[a-d]'] | +----------------------------+ | 1 | +----------------------------+
mysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE']; +---------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE'] | +---------------------------------------+ | 1 | +---------------------------------------+ mysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs]; +------------------------------------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs] | +------------------------------------------------------------------+ | 0 | +------------------------------------------------------------------+
2 có thể được chỉ định với các ký tựmysql> SELECT REGEXP_LIKE['Michael!', '.*']; +-------------------------------+ | REGEXP_LIKE['Michael!', '.*'] | +-------------------------------+ | 1 | +-------------------------------+ mysql> SELECT REGEXP_LIKE['new*\n*line', 'new\\*.\\*line']; +----------------------------------------------+ | REGEXP_LIKE['new*\n*line', 'new\\*.\\*line'] | +----------------------------------------------+ | 0 | +----------------------------------------------+ mysql> SELECT REGEXP_LIKE['a', '^[a-d]']; +----------------------------+ | REGEXP_LIKE['a', '^[a-d]'] | +----------------------------+ | 1 | +----------------------------+
9 hoặcmysql> SELECT REGEXP_REPLACE['a b c', 'b', 'X']; +-----------------------------------+ | REGEXP_REPLACE['a b c', 'b', 'X'] | +-----------------------------------+ | a X c | +-----------------------------------+ mysql> SELECT REGEXP_REPLACE['abc def ghi', '[a-z]+', 'X', 1, 3]; +----------------------------------------------------+ | REGEXP_REPLACE['abc def ghi', '[a-z]+', 'X', 1, 3] | +----------------------------------------------------+ | abc def X | +----------------------------------------------------+
0 để ghi đè độ nhạy của trường hợp mặc định. Ngoại lệ: Nếu một trong hai đối số là một chuỗi nhị phân, các đối số được xử lý theo kiểu nhạy cảm trường hợp như các chuỗi nhị phân, ngay cả khimysql> SELECT REGEXP_SUBSTR['abc def ghi', '[a-z]+']; +----------------------------------------+ | REGEXP_SUBSTR['abc def ghi', '[a-z]+'] | +----------------------------------------+ | abc | +----------------------------------------+ mysql> SELECT REGEXP_SUBSTR['abc def ghi', '[a-z]+', 1, 3]; +----------------------------------------------+ | REGEXP_SUBSTR['abc def ghi', '[a-z]+', 1, 3] | +----------------------------------------------+ | ghi | +----------------------------------------------+
2 chứa ký tựmysql> SELECT REGEXP_LIKE['Michael!', '.*']; +-------------------------------+ | REGEXP_LIKE['Michael!', '.*'] | +-------------------------------+ | 1 | +-------------------------------+ mysql> SELECT REGEXP_LIKE['new*\n*line', 'new\\*.\\*line']; +----------------------------------------------+ | REGEXP_LIKE['new*\n*line', 'new\\*.\\*line'] | +----------------------------------------------+ | 0 | +----------------------------------------------+ mysql> SELECT REGEXP_LIKE['a', '^[a-d]']; +----------------------------+ | REGEXP_LIKE['a', '^[a-d]'] | +----------------------------+ | 1 | +----------------------------+
0.mysql> SELECT REGEXP_SUBSTR['abc def ghi', '[a-z]+']; +----------------------------------------+ | REGEXP_SUBSTR['abc def ghi', '[a-z]+'] | +----------------------------------------+ | abc | +----------------------------------------+ mysql> SELECT REGEXP_SUBSTR['abc def ghi', '[a-z]+', 1, 3]; +----------------------------------------------+ | REGEXP_SUBSTR['abc def ghi', '[a-z]+', 1, 3] | +----------------------------------------------+ | ghi | +----------------------------------------------+
Ghi chú
MySQL sử dụng C Escape Cú pháp trong chuỗi [ví dụ:
expr
7 để biểu thị ký tự dòng mới]. Nếu bạn muốn đối số
7 hoặcmysql> SELECT REGEXP_INSTR['dog cat dog', 'dog']; +------------------------------------+ | REGEXP_INSTR['dog cat dog', 'dog'] | +------------------------------------+ | 1 | +------------------------------------+ mysql> SELECT REGEXP_INSTR['dog cat dog', 'dog', 2]; +---------------------------------------+ | REGEXP_INSTR['dog cat dog', 'dog', 2] | +---------------------------------------+ | 9 | +---------------------------------------+ mysql> SELECT REGEXP_INSTR['aa aaa aaaa', 'a{2}']; +-------------------------------------+ | REGEXP_INSTR['aa aaa aaaa', 'a{2}'] | +-------------------------------------+ | 1 | +-------------------------------------+ mysql> SELECT REGEXP_INSTR['aa aaa aaaa', 'a{4}']; +-------------------------------------+ | REGEXP_INSTR['aa aaa aaaa', 'a{4}'] | +-------------------------------------+ | 8 | +-------------------------------------+
pat
của mình chứa mộtpat
0 theo nghĩa đen, bạn phải nhân đôi nó. [Trừ khi chế độpat
1 SQL được bật, trong trường hợp đó không sử dụng ký tự thoát.]mysql> SELECT REGEXP_LIKE['Michael!', '.*']; +-------------------------------+ | REGEXP_LIKE['Michael!', '.*'] | +-------------------------------+ | 1 | +-------------------------------+ mysql> SELECT REGEXP_LIKE['new*\n*line', 'new\\*.\\*line']; +----------------------------------------------+ | REGEXP_LIKE['new*\n*line', 'new\\*.\\*line'] | +----------------------------------------------+ | 0 | +----------------------------------------------+ mysql> SELECT REGEXP_LIKE['a', '^[a-d]']; +----------------------------+ | REGEXP_LIKE['a', '^[a-d]'] | +----------------------------+ | 1 | +----------------------------+
mysql> SELECT REGEXP_LIKE['abc', 'ABC']; +---------------------------+ | REGEXP_LIKE['abc', 'ABC'] | +---------------------------+ | 1 | +---------------------------+ mysql> SELECT REGEXP_LIKE['abc', 'ABC', 'c']; +--------------------------------+ | REGEXP_LIKE['abc', 'ABC', 'c'] | +--------------------------------+ | 0 | +--------------------------------+
RegEXP_REPLACE [________ 17,
pat
, ________ 84 [, ________ 29 [, ________ 30 [,
2]]]]]mysql> SELECT REGEXP_LIKE['Michael!', '.*']; +-------------------------------+ | REGEXP_LIKE['Michael!', '.*'] | +-------------------------------+ | 1 | +-------------------------------+ mysql> SELECT REGEXP_LIKE['new*\n*line', 'new\\*.\\*line']; +----------------------------------------------+ | REGEXP_LIKE['new*\n*line', 'new\\*.\\*line'] | +----------------------------------------------+ | 0 | +----------------------------------------------+ mysql> SELECT REGEXP_LIKE['a', '^[a-d]']; +----------------------------+ | REGEXP_LIKE['a', '^[a-d]'] | +----------------------------+ | 1 | +----------------------------+
Thay thế các lần xuất hiện trong chuỗi
7 khớp với biểu thức chính quy được chỉ định bởi mẫumysql> SELECT REGEXP_INSTR['dog cat dog', 'dog']; +------------------------------------+ | REGEXP_INSTR['dog cat dog', 'dog'] | +------------------------------------+ | 1 | +------------------------------------+ mysql> SELECT REGEXP_INSTR['dog cat dog', 'dog', 2]; +---------------------------------------+ | REGEXP_INSTR['dog cat dog', 'dog', 2] | +---------------------------------------+ | 9 | +---------------------------------------+ mysql> SELECT REGEXP_INSTR['aa aaa aaaa', 'a{2}']; +-------------------------------------+ | REGEXP_INSTR['aa aaa aaaa', 'a{2}'] | +-------------------------------------+ | 1 | +-------------------------------------+ mysql> SELECT REGEXP_INSTR['aa aaa aaaa', 'a{4}']; +-------------------------------------+ | REGEXP_INSTR['aa aaa aaaa', 'a{4}'] | +-------------------------------------+ | 8 | +-------------------------------------+
pat
với chuỗi thay thếpat
4 và trả về chuỗi kết quả. Nếu
7,mysql> SELECT REGEXP_INSTR['dog cat dog', 'dog']; +------------------------------------+ | REGEXP_INSTR['dog cat dog', 'dog'] | +------------------------------------+ | 1 | +------------------------------------+ mysql> SELECT REGEXP_INSTR['dog cat dog', 'dog', 2]; +---------------------------------------+ | REGEXP_INSTR['dog cat dog', 'dog', 2] | +---------------------------------------+ | 9 | +---------------------------------------+ mysql> SELECT REGEXP_INSTR['aa aaa aaaa', 'a{2}']; +-------------------------------------+ | REGEXP_INSTR['aa aaa aaaa', 'a{2}'] | +-------------------------------------+ | 1 | +-------------------------------------+ mysql> SELECT REGEXP_INSTR['aa aaa aaaa', 'a{4}']; +-------------------------------------+ | REGEXP_INSTR['aa aaa aaaa', 'a{4}'] | +-------------------------------------+ | 8 | +-------------------------------------+
pat
hoặcpat
4 là
1, giá trị trả về làmysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE']; +---------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE'] | +---------------------------------------+ | 1 | +---------------------------------------+ mysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs]; +------------------------------------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs] | +------------------------------------------------------------------+ | 0 | +------------------------------------------------------------------+
1.mysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE']; +---------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE'] | +---------------------------------------+ | 1 | +---------------------------------------+ mysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs]; +------------------------------------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs] | +------------------------------------------------------------------+ | 0 | +------------------------------------------------------------------+
expr
6 lấy các đối số tùy chọn này:
9: Vị trí trongmysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE']; +---------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE'] | +---------------------------------------+ | 1 | +---------------------------------------+ mysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs]; +------------------------------------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs] | +------------------------------------------------------------------+ | 0 | +------------------------------------------------------------------+
7 để bắt đầu tìm kiếm. Nếu bị bỏ qua, mặc định là 1.mysql> SELECT REGEXP_INSTR['dog cat dog', 'dog']; +------------------------------------+ | REGEXP_INSTR['dog cat dog', 'dog'] | +------------------------------------+ | 1 | +------------------------------------+ mysql> SELECT REGEXP_INSTR['dog cat dog', 'dog', 2]; +---------------------------------------+ | REGEXP_INSTR['dog cat dog', 'dog', 2] | +---------------------------------------+ | 9 | +---------------------------------------+ mysql> SELECT REGEXP_INSTR['aa aaa aaaa', 'a{2}']; +-------------------------------------+ | REGEXP_INSTR['aa aaa aaaa', 'a{2}'] | +-------------------------------------+ | 1 | +-------------------------------------+ mysql> SELECT REGEXP_INSTR['aa aaa aaaa', 'a{4}']; +-------------------------------------+ | REGEXP_INSTR['aa aaa aaaa', 'a{4}'] | +-------------------------------------+ | 8 | +-------------------------------------+
0: Sự xuất hiện của một trận đấu để thay thế. Nếu bị bỏ qua, mặc định là 0 [có nghĩa là thay thế tất cả các lần xuất hiện].“replace all occurrences”].mysql> SELECT REGEXP_LIKE['Michael!', '.*']; +-------------------------------+ | REGEXP_LIKE['Michael!', '.*'] | +-------------------------------+ | 1 | +-------------------------------+ mysql> SELECT REGEXP_LIKE['new*\n*line', 'new\\*.\\*line']; +----------------------------------------------+ | REGEXP_LIKE['new*\n*line', 'new\\*.\\*line'] | +----------------------------------------------+ | 0 | +----------------------------------------------+ mysql> SELECT REGEXP_LIKE['a', '^[a-d]']; +----------------------------+ | REGEXP_LIKE['a', '^[a-d]'] | +----------------------------+ | 1 | +----------------------------+
2: Một chuỗi chỉ định cách thực hiện khớp. Ý nghĩa được mô tả chomysql> SELECT REGEXP_LIKE['Michael!', '.*']; +-------------------------------+ | REGEXP_LIKE['Michael!', '.*'] | +-------------------------------+ | 1 | +-------------------------------+ mysql> SELECT REGEXP_LIKE['new*\n*line', 'new\\*.\\*line']; +----------------------------------------------+ | REGEXP_LIKE['new*\n*line', 'new\\*.\\*line'] | +----------------------------------------------+ | 0 | +----------------------------------------------+ mysql> SELECT REGEXP_LIKE['a', '^[a-d]']; +----------------------------+ | REGEXP_LIKE['a', '^[a-d]'] | +----------------------------+ | 1 | +----------------------------+
5.mysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE']; +---------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE'] | +---------------------------------------+ | 1 | +---------------------------------------+ mysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs]; +------------------------------------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs] | +------------------------------------------------------------------+ | 0 | +------------------------------------------------------------------+
Trước MySQL 8.0.17, kết quả được trả về bởi hàm này đã sử dụng bộ ký tự
02; Trong MySQL 8.0.17 trở lên, bộ ký tự và đối chiếu của biểu thức được tìm kiếm cho các trận đấu được sử dụng. [Lỗi #94203, lỗi #29308212]mysql> SELECT REGEXP_INSTR['dog cat dog', 'dog']; +------------------------------------+ | REGEXP_INSTR['dog cat dog', 'dog'] | +------------------------------------+ | 1 | +------------------------------------+ mysql> SELECT REGEXP_INSTR['dog cat dog', 'dog', 2]; +---------------------------------------+ | REGEXP_INSTR['dog cat dog', 'dog', 2] | +---------------------------------------+ | 9 | +---------------------------------------+ mysql> SELECT REGEXP_INSTR['aa aaa aaaa', 'a{2}']; +-------------------------------------+ | REGEXP_INSTR['aa aaa aaaa', 'a{2}'] | +-------------------------------------+ | 1 | +-------------------------------------+ mysql> SELECT REGEXP_INSTR['aa aaa aaaa', 'a{4}']; +-------------------------------------+ | REGEXP_INSTR['aa aaa aaaa', 'a{4}'] | +-------------------------------------+ | 8 | +-------------------------------------+
Để biết thêm thông tin về cách kết hợp phù hợp, hãy xem mô tả cho
5.mysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE']; +---------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE'] | +---------------------------------------+ | 1 | +---------------------------------------+ mysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs]; +------------------------------------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs] | +------------------------------------------------------------------+ | 0 | +------------------------------------------------------------------+
mysql> SELECT REGEXP_REPLACE['a b c', 'b', 'X']; +-----------------------------------+ | REGEXP_REPLACE['a b c', 'b', 'X'] | +-----------------------------------+ | a X c | +-----------------------------------+ mysql> SELECT REGEXP_REPLACE['abc def ghi', '[a-z]+', 'X', 1, 3]; +----------------------------------------------------+ | REGEXP_REPLACE['abc def ghi', '[a-z]+', 'X', 1, 3] | +----------------------------------------------------+ | abc def X | +----------------------------------------------------+
Regexp_substr [________ 17, ________ 8 [, ________ 29 [, ________ 30 [,
2]]]]]]mysql> SELECT REGEXP_LIKE['Michael!', '.*']; +-------------------------------+ | REGEXP_LIKE['Michael!', '.*'] | +-------------------------------+ | 1 | +-------------------------------+ mysql> SELECT REGEXP_LIKE['new*\n*line', 'new\\*.\\*line']; +----------------------------------------------+ | REGEXP_LIKE['new*\n*line', 'new\\*.\\*line'] | +----------------------------------------------+ | 0 | +----------------------------------------------+ mysql> SELECT REGEXP_LIKE['a', '^[a-d]']; +----------------------------+ | REGEXP_LIKE['a', '^[a-d]'] | +----------------------------+ | 1 | +----------------------------+
Trả về chuỗi con của chuỗi
7 phù hợp với biểu thức chính quy được chỉ định bởi mẫumysql> SELECT REGEXP_INSTR['dog cat dog', 'dog']; +------------------------------------+ | REGEXP_INSTR['dog cat dog', 'dog'] | +------------------------------------+ | 1 | +------------------------------------+ mysql> SELECT REGEXP_INSTR['dog cat dog', 'dog', 2]; +---------------------------------------+ | REGEXP_INSTR['dog cat dog', 'dog', 2] | +---------------------------------------+ | 9 | +---------------------------------------+ mysql> SELECT REGEXP_INSTR['aa aaa aaaa', 'a{2}']; +-------------------------------------+ | REGEXP_INSTR['aa aaa aaaa', 'a{2}'] | +-------------------------------------+ | 1 | +-------------------------------------+ mysql> SELECT REGEXP_INSTR['aa aaa aaaa', 'a{4}']; +-------------------------------------+ | REGEXP_INSTR['aa aaa aaaa', 'a{4}'] | +-------------------------------------+ | 8 | +-------------------------------------+
pat
,
1 nếu không có khớp. Nếumysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE']; +---------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE'] | +---------------------------------------+ | 1 | +---------------------------------------+ mysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs]; +------------------------------------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs] | +------------------------------------------------------------------+ | 0 | +------------------------------------------------------------------+
7 hoặcmysql> SELECT REGEXP_INSTR['dog cat dog', 'dog']; +------------------------------------+ | REGEXP_INSTR['dog cat dog', 'dog'] | +------------------------------------+ | 1 | +------------------------------------+ mysql> SELECT REGEXP_INSTR['dog cat dog', 'dog', 2]; +---------------------------------------+ | REGEXP_INSTR['dog cat dog', 'dog', 2] | +---------------------------------------+ | 9 | +---------------------------------------+ mysql> SELECT REGEXP_INSTR['aa aaa aaaa', 'a{2}']; +-------------------------------------+ | REGEXP_INSTR['aa aaa aaaa', 'a{2}'] | +-------------------------------------+ | 1 | +-------------------------------------+ mysql> SELECT REGEXP_INSTR['aa aaa aaaa', 'a{4}']; +-------------------------------------+ | REGEXP_INSTR['aa aaa aaaa', 'a{4}'] | +-------------------------------------+ | 8 | +-------------------------------------+
pat
là
1, giá trị trả về làmysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE']; +---------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE'] | +---------------------------------------+ | 1 | +---------------------------------------+ mysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs]; +------------------------------------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs] | +------------------------------------------------------------------+ | 0 | +------------------------------------------------------------------+
1.mysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE']; +---------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE'] | +---------------------------------------+ | 1 | +---------------------------------------+ mysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs]; +------------------------------------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs] | +------------------------------------------------------------------+ | 0 | +------------------------------------------------------------------+
16 lấy các đối số tùy chọn này:mysql> SELECT REGEXP_INSTR['dog cat dog', 'dog']; +------------------------------------+ | REGEXP_INSTR['dog cat dog', 'dog'] | +------------------------------------+ | 1 | +------------------------------------+ mysql> SELECT REGEXP_INSTR['dog cat dog', 'dog', 2]; +---------------------------------------+ | REGEXP_INSTR['dog cat dog', 'dog', 2] | +---------------------------------------+ | 9 | +---------------------------------------+ mysql> SELECT REGEXP_INSTR['aa aaa aaaa', 'a{2}']; +-------------------------------------+ | REGEXP_INSTR['aa aaa aaaa', 'a{2}'] | +-------------------------------------+ | 1 | +-------------------------------------+ mysql> SELECT REGEXP_INSTR['aa aaa aaaa', 'a{4}']; +-------------------------------------+ | REGEXP_INSTR['aa aaa aaaa', 'a{4}'] | +-------------------------------------+ | 8 | +-------------------------------------+
9: Vị trí trongmysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE']; +---------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE'] | +---------------------------------------+ | 1 | +---------------------------------------+ mysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs]; +------------------------------------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs] | +------------------------------------------------------------------+ | 0 | +------------------------------------------------------------------+
7 để bắt đầu tìm kiếm. Nếu bị bỏ qua, mặc định là 1.mysql> SELECT REGEXP_INSTR['dog cat dog', 'dog']; +------------------------------------+ | REGEXP_INSTR['dog cat dog', 'dog'] | +------------------------------------+ | 1 | +------------------------------------+ mysql> SELECT REGEXP_INSTR['dog cat dog', 'dog', 2]; +---------------------------------------+ | REGEXP_INSTR['dog cat dog', 'dog', 2] | +---------------------------------------+ | 9 | +---------------------------------------+ mysql> SELECT REGEXP_INSTR['aa aaa aaaa', 'a{2}']; +-------------------------------------+ | REGEXP_INSTR['aa aaa aaaa', 'a{2}'] | +-------------------------------------+ | 1 | +-------------------------------------+ mysql> SELECT REGEXP_INSTR['aa aaa aaaa', 'a{4}']; +-------------------------------------+ | REGEXP_INSTR['aa aaa aaaa', 'a{4}'] | +-------------------------------------+ | 8 | +-------------------------------------+
0: Sự xuất hiện của một trận đấu để thay thế. Nếu bị bỏ qua, mặc định là 0 [có nghĩa là thay thế tất cả các lần xuất hiện].mysql> SELECT REGEXP_LIKE['Michael!', '.*']; +-------------------------------+ | REGEXP_LIKE['Michael!', '.*'] | +-------------------------------+ | 1 | +-------------------------------+ mysql> SELECT REGEXP_LIKE['new*\n*line', 'new\\*.\\*line']; +----------------------------------------------+ | REGEXP_LIKE['new*\n*line', 'new\\*.\\*line'] | +----------------------------------------------+ | 0 | +----------------------------------------------+ mysql> SELECT REGEXP_LIKE['a', '^[a-d]']; +----------------------------+ | REGEXP_LIKE['a', '^[a-d]'] | +----------------------------+ | 1 | +----------------------------+
2: Một chuỗi chỉ định cách thực hiện khớp. Ý nghĩa được mô tả chomysql> SELECT REGEXP_LIKE['Michael!', '.*']; +-------------------------------+ | REGEXP_LIKE['Michael!', '.*'] | +-------------------------------+ | 1 | +-------------------------------+ mysql> SELECT REGEXP_LIKE['new*\n*line', 'new\\*.\\*line']; +----------------------------------------------+ | REGEXP_LIKE['new*\n*line', 'new\\*.\\*line'] | +----------------------------------------------+ | 0 | +----------------------------------------------+ mysql> SELECT REGEXP_LIKE['a', '^[a-d]']; +----------------------------+ | REGEXP_LIKE['a', '^[a-d]'] | +----------------------------+ | 1 | +----------------------------+
5.mysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE']; +---------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE'] | +---------------------------------------+ | 1 | +---------------------------------------+ mysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs]; +------------------------------------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs] | +------------------------------------------------------------------+ | 0 | +------------------------------------------------------------------+
Trước MySQL 8.0.17, kết quả được trả về bởi hàm này đã sử dụng bộ ký tự
02; Trong MySQL 8.0.17 trở lên, bộ ký tự và đối chiếu của biểu thức được tìm kiếm cho các trận đấu được sử dụng. [Lỗi #94203, lỗi #29308212]mysql> SELECT REGEXP_INSTR['dog cat dog', 'dog']; +------------------------------------+ | REGEXP_INSTR['dog cat dog', 'dog'] | +------------------------------------+ | 1 | +------------------------------------+ mysql> SELECT REGEXP_INSTR['dog cat dog', 'dog', 2]; +---------------------------------------+ | REGEXP_INSTR['dog cat dog', 'dog', 2] | +---------------------------------------+ | 9 | +---------------------------------------+ mysql> SELECT REGEXP_INSTR['aa aaa aaaa', 'a{2}']; +-------------------------------------+ | REGEXP_INSTR['aa aaa aaaa', 'a{2}'] | +-------------------------------------+ | 1 | +-------------------------------------+ mysql> SELECT REGEXP_INSTR['aa aaa aaaa', 'a{4}']; +-------------------------------------+ | REGEXP_INSTR['aa aaa aaaa', 'a{4}'] | +-------------------------------------+ | 8 | +-------------------------------------+
Để biết thêm thông tin về cách kết hợp phù hợp, hãy xem mô tả cho
5.mysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE']; +---------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE'] | +---------------------------------------+ | 1 | +---------------------------------------+ mysql> SELECT REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs]; +------------------------------------------------------------------+ | REGEXP_LIKE['CamelCase', 'CAMELCASE' COLLATE utf8mb4_0900_as_cs] | +------------------------------------------------------------------+ | 0 | +------------------------------------------------------------------+
mysql> SELECT REGEXP_SUBSTR['abc def ghi', '[a-z]+']; +----------------------------------------+ | REGEXP_SUBSTR['abc def ghi', '[a-z]+'] | +----------------------------------------+ | abc | +----------------------------------------+ mysql> SELECT REGEXP_SUBSTR['abc def ghi', '[a-z]+', 1, 3]; +----------------------------------------------+ | REGEXP_SUBSTR['abc def ghi', '[a-z]+', 1, 3] | +----------------------------------------------+ | ghi | +----------------------------------------------+