如何在MySQL中替换正则表达式模式 [英] How to replace a regex pattern in MySQL

问题描述

我有一个名为myTable的表，该表具有一个名为col1的列.此列包含以下格式的数据:(1或2位数字)(连字符)(8位数字).

I have a table called myTable which has a column called col1. This column contains data in this format: (1 or 2 digits)(hyphen)(8 digits).

我想替换此列中的所有数据，并将连字符之前的所有内容替换为4，所以这是一个示例:

I want to replace all the data in this column and replace everything before hyphen with 4, so this is an example:

--------------------------------
|  old values   |  New Values  |
--------------------------------
| 1-654283568  =>  4-654283568 |
| 2-467862833  =>  4-467862833 |
| 8-478934293  =>  4-478934293 |
| 12-573789475 =>  4-573789475 |
| 16-574738575 =>  4-574738575 |
--------------------------------

我正在使用MySQL 5.7.19，我相信REGEXP_REPLACE在MySQL 8+中可用...不确定如何实现?

I am using MySQL 5.7.19, I believe REGEXP_REPLACE is available in MySQL Version 8+... not sure how this can be achieved?

推荐答案

您不需要正则表达式；您可以使用 SUBSTRING_INDEX 提取连字符后的所有内容并将4-连接到该字符串:

You don't need regex; you can use SUBSTRING_INDEX to extract everything after the hyphen and concatenate 4- to that:

UPDATE myTable
SET col1 = CONCAT('4-', SUBSTRING_INDEX(col1, '-', -1))

dbfiddle上的演示

无论连字符后面的字符数如何，此方法都可以使用.

This will work regardless of the number of characters after the hyphen.

这篇关于如何在MySQL中替换正则表达式模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！