REGEXP_REPLACE MySQL thay thế

MySQL cho phép bạn thay thế một lần xuất hiện của chuỗi con bằng hàm REPLACE(). Đôi khi, bạn có thể cần thay thế nhiều hoặc tất cả các lần xuất hiện của chuỗi con hoặc thậm chí nhiều chuỗi phù hợp bằng một chuỗi đã cho. Bạn có thể dễ dàng thực hiện việc này bằng cách sử dụng biểu thức chính quy thay thế. Trong bài viết này, chúng ta sẽ tìm hiểu cách thay thế biểu thức chính quy trong MySQL

Cách thực hiện Thay thế biểu thức chính quy trong MySQL

Nếu bạn đang sử dụng MySQL 8+, bạn có thể sử dụng trực tiếp REGEXP_REPLACE. Đây là cú pháp cho nó

REGEXP_REPLACE(col, regexp, replace)

Đây là một ví dụ đơn giản để thay thế các phần của chuỗi ký tự bằng biểu thức chính quy

mysql> SELECT REGEXP_REPLACE("stackoverflow", "(stack)(over)(flow)", '\\2 - \\1 - \\3')

Bạn sẽ nhận được đầu ra sau

over - stack - flow

Ví dụ: nếu bạn cần sử dụng chức năng này trong câu lệnh CẬP NHẬT, đây là cách thực hiện việc này

UPDATE `table_name` 
SET column_name_to_find=REGEXP_REPLACE(column_name_to_find, 'string-to-find', 'string-to-replace') 
WHERE column_name_to_find REGEXP 'string-to-find'

Nếu bạn sử dụng MySQL<8, thì bạn cần sử dụng hàm do người dùng xác định để triển khai logic để thay thế chuỗi dựa trên biểu thức chính quy. Có rất nhiều gói do người dùng xác định có sẵn với các loại chức năng này trực tuyến. Bạn có thể sử dụng gói mysql-udf-regexp cho mục đích này. Nó cung cấp 4 chức năng sau, tương tự như các chức năng có sẵn trong Oracle

REGEXP_LIKE(text, pattern [, mode])
REGEXP_SUBSTR(text, pattern [,position [,occurence [,mode]]])
REGEXP_INSTR?(text, pattern [,position [,occurence [,return_end [,mode]]]])
REGEXP_REPLACE?(text, pattern, replace [,position [,occurence [,return_end [,mode]]])

Trong bài viết này, chúng ta đã học cách sử dụng biểu thức chính quy để thay thế chuỗi và chuỗi con trong MySQL

Hàm REGEXP_REPLACE() của MySQL thay thế các lần xuất hiện trong chuỗi khớp với biểu thức chính quy được chỉ định bởi mẫu bằng chuỗi thay thế thay thế và trả về chuỗi kết quả. Nếu chuỗi hoặc mẫu là NULL, giá trị trả về sẽ là NULL

Theo mặc định, hàm REGEXP_REPLACE() thực hiện khớp không phân biệt chữ hoa chữ thường, trừ khi được sử dụng với chuỗi nhị phân

cú pháp

REGEXP_REPLACE(string, pattern, replace, 
               position, occurrence, match_type)

Thông số

stringRequired.Chỉ định chuỗi để tìm kiếm. patternRequired.Chỉ định thông tin phù hợp với biểu thức chính quy. Xem bảng bên dưới để biết cú pháp mẫu. replaceRequired.Chỉ định chuỗi con thay thế.

mysql> SELECT REGEXP_REPLACE('a b c', 'b', 'X');
Result: 'a X c'

mysql> SELECT REGEXP_REPLACE('abc def ghi', '[a-z]+', 'X', 1, 3);
Result: 'abc def X'

mysql> SELECT REGEXP_REPLACE('abc123ghi', '[0-9]+', '');
Result: 'abcghi'

mysql> SELECT REGEXP_REPLACE('titlebody', '<.+?>','');
Result: 'titlebody'

0_______6_______1Chỉ định vị trí trong chuỗi để bắt đầu tìm kiếm. Mặc định là 1.

mysql> SELECT REGEXP_REPLACE('a b c', 'b', 'X');
Result: 'a X c'

mysql> SELECT REGEXP_REPLACE('abc def ghi', '[a-z]+', 'X', 1, 3);
Result: 'abc def X'

mysql> SELECT REGEXP_REPLACE('abc123ghi', '[0-9]+', '');
Result: 'abcghi'

mysql> SELECT REGEXP_REPLACE('titlebody', '<.+?>','');
Result: 'titlebody'

2

mysql> SELECT REGEXP_REPLACE('a b c', 'b', 'X');
Result: 'a X c'

mysql> SELECT REGEXP_REPLACE('abc def ghi', '[a-z]+', 'X', 1, 3);
Result: 'abc def X'

mysql> SELECT REGEXP_REPLACE('abc123ghi', '[0-9]+', '');
Result: 'abcghi'

mysql> SELECT REGEXP_REPLACE('titlebody', '<.+?>','');
Result: 'titlebody'

1Chỉ định sự xuất hiện của trận đấu để thay thế. 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_REPLACE('a b c', 'b', 'X');
Result: 'a X c'

mysql> SELECT REGEXP_REPLACE('abc def ghi', '[a-z]+', 'X', 1, 3);
Result: 'abc def X'

mysql> SELECT REGEXP_REPLACE('abc123ghi', '[0-9]+', '');
Result: 'abcghi'

mysql> SELECT REGEXP_REPLACE('titlebody', '<.+?>','');
Result: 'titlebody'

4

mysql> SELECT REGEXP_REPLACE('a b c', 'b', 'X');
Result: 'a X c'

mysql> SELECT REGEXP_REPLACE('abc def ghi', '[a-z]+', 'X', 1, 3);
Result: 'abc def X'

mysql> SELECT REGEXP_REPLACE('abc123ghi', '[0-9]+', '');
Result: 'abcghi'

mysql> SELECT REGEXP_REPLACE('titlebody', '<.+?>','');
Result: 'titlebody'

1Nêu cách thực hiện so khớp. Nó có thể nhận các giá trị sau

• c  -  Khớp phân biệt chữ hoa chữ thường
• i  -  So khớp không phân biệt chữ hoa chữ thường
• m  -  Chế độ nhiều dòng. Nhận biết các đầu cuối dòng trong chuỗi. Hành vi mặc định là chỉ khớp các dấu kết thúc dòng ở đầu và cuối của biểu thức chuỗi
• n  -  Các. ký tự khớp với dấu kết thúc dòng. Mặc định là cho. phù hợp để dừng ở cuối dòng
• u  -  Kết thúc dòng chỉ dành cho Unix. Chỉ ký tự dòng mới được nhận dạng là một dòng kết thúc bởi. Toán tử đối sánh , ^ và $

Để biết thêm thông tin về tham số này, hãy xem các ví dụ về hàm REGEXP_LIKE()

Các mẫu biểu thức chính quy

SymbolDescription^Khớp với phần đầu của chuỗi. $ Khớp với phần cuối của một chuỗi. *Không khớp hoặc nhiều lần xuất hiện. +Khớp một hoặc nhiều lần xuất hiện. ?Trận đấu không hoặc một lần xuất hiện. Khớp với bất kỳ ký tự nào ngoại trừ NULL. Được sử dụng như một "HOẶC" để chỉ định nhiều hơn một phương án thay thế. [ ] Khớp với bất kỳ ký tự đơn nào được chỉ định trong []. [^ ]Khớp với bất kỳ ký tự đơn nào không được chỉ định trong []. -Đại diện cho một loạt các nhân vật. ( )Được sử dụng để nhóm các biểu thức thành một biểu thức con. {m}Trận đấu m lần. {m,}Trận đấu ít nhất m lần. {m,n}Trận đấu ít nhất m lần, nhưng không quá n lần. \nn là một số từ 1 đến 9. Khớp với biểu thức con thứ n được tìm thấy trong ( ) trước khi gặp \n. \dKhớp một ký tự chữ số. \DKhớp một ký tự không có chữ số. \wKhớp một ký tự từ. \WKhớp một ký tự không phải từ. \sKhớp với một ký tự khoảng trắng. \Smatches một ký tự không phải khoảng trắng. *?Khớp mẫu trước không hoặc nhiều lần xuất hiện. +?Khớp mẫu trước đó một hoặc nhiều lần xuất hiện. ??Khớp mẫu trước không hoặc một lần xuất hiện. {n}?Khớp mẫu trước đó n lần. {n,}?Khớp mẫu trước đó ít nhất n lần. {n,m}?Khớp mẫu trước đó ít nhất n lần, nhưng không nhiều hơn m lần. [. ] So khớp một phần tử đối chiếu có thể có nhiều hơn một ký tự. [=character_class=]Đại diện cho một lớp tương đương. Nó khớp với tất cả các ký tự có cùng giá trị đối chiếu, kể cả chính nó. [. nhân vật_class. ]Đại diện cho một lớp ký tự khớp với tất cả các ký tự thuộc lớp đó

Danh sách tên lớp tiêu chuẩn

Tên lớp ký tự Ý nghĩa chữ số Ký tự chữ và số alphaKý tự chữ cái chỗ trốngKý tự khoảng trắngcntrlKý tự điều khiểnchữ sốKý tự chữ sốđồ thịKý tự đồ họachữ thườngKý tự chữ cái viết thườnginKý tự đồ họa hoặc dấu cáchKý tự chấm câukhông gianKhoảng cách, tab, dòng mới và xuống dòngKý tự chữ cái viết hoax chữ sốKý tự thập lục phân

Thí dụ. REGEXP_REPLACE() ví dụ

Ví dụ dưới đây cho thấy cách sử dụng hàm REGEXP_REPLACE()

mysql> SELECT REGEXP_REPLACE('a b c', 'b', 'X');
Result: 'a X c'

mysql> SELECT REGEXP_REPLACE('abc def ghi', '[a-z]+', 'X', 1, 3);
Result: 'abc def X'

mysql> SELECT REGEXP_REPLACE('abc123ghi', '[0-9]+', '');
Result: 'abcghi'

mysql> SELECT REGEXP_REPLACE('titlebody', '<.+?>','');
Result: 'titlebody'

Thí dụ. Phù hợp với nhiều hơn một lựa chọn thay thế

Hãy xem xét một bảng cơ sở dữ liệu được gọi là Nhân viên với các bản ghi sau

EmpIDNameThành phốTuổi1John SmithLondon252Marry KnightNew York243Jo WilliamsParis274Smith FischerAmsterdam305Aman GuptaNew Delhi28

Truy vấn dưới đây được sử dụng để thay thế tất cả các nguyên âm trong bản ghi của cột Thành phố bằng '@'. Để chỉ định nhiều hơn một phương án trong mẫu,. Được sử dụng

SELECT *, 
REGEXP_REPLACE(City, 'a|e|i|o|u', '@') AS Replaced_City
FROM Employee

Điều này sẽ tạo ra một kết quả tương tự như

EmpIDNameThành phốTuổiReplaced_City1John SmithLondon25L@nd@n2Marry KnightNew York24N@w Y@rk3Jo WilliamsParis27P@r@s4Smith FischerAmsterdam30@mst@rd@m5Aman GuptaNew Delhi28N@w D@lh@

Thí dụ. Phù hợp với từ

Xem xét bảng thảo luận ở trên. Truy vấn dưới đây được sử dụng để thay thế từ đầu tiên (tên) từ cột Tên bằng 'Mr. '. Để chỉ định phạm vi ký tự, [  ] được sử dụng

SELECT *, 
REGEXP_REPLACE(Name, '[a-z]+', 'Mr.', 1, 1) AS ReplacedName
FROM Employee

Điều này sẽ tạo ra một kết quả tương tự như

EmpIDNameCityAgeReplacedName1John SmithLondon25Mr. Smith2Marry KnightNew York24Mr. Hiệp sĩ3Jo WilliamsParis27Mr. Williams4Smith FischerAmsterdam30Mr. Fischer5Aman GuptaNew Delhi28Mr. Gupta

Làm cách nào để thay thế regex trong MySQL?

MySQL có regex không?

Sự khác biệt giữa REGEXP và like trong MySQL là gì?

Bạn có thể sử dụng regex trong thay thế SQL không?