如何删除字符串开头的<br>s？

Question

这是我的桌子：

-- mytb
+----+---------------------------+
| id |         content           |
+----+---------------------------+
| 1  | <br> whatever ..          |
| 2  | whatever <br> whatever .. |
| 3  |    <br> whatever ..       |
| 4  | <br> whatever <br> ..     |
+----+---------------------------+

我需要删除字符串开头的所有<br>。我可以在PHP中使用^指定它。我如何在MySQL中做同样的事情呢？

update mytb set content = trim(content)

它只会移除被包围的空间。

Answer 1

您可以使用这样的查询：

SELECT REGEXP_REPLACE('<br>Hello<br>world<br>','^(<br>)*(.*?)(<br>)*$','\\2');

样本

MariaDB [(none)]> SELECT REGEXP_REPLACE('<br>Hello<br>world<br>','^(<br>)*(.*?)(<br>)*$','\\2');
+------------------------------------------------------------------------+
| REGEXP_REPLACE('<br>Hello<br>world<br>','^(<br>)*(.*?)(<br>)*$','\\2') |
+------------------------------------------------------------------------+
| Hello<br>world                                                         |
+------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT REGEXP_REPLACE('Hello<br>world<br>','^(<br>)*(.*?)(<br>)*$','\\2');
+--------------------------------------------------------------------+
| REGEXP_REPLACE('Hello<br>world<br>','^(<br>)*(.*?)(<br>)*$','\\2') |
+--------------------------------------------------------------------+
| Hello<br>world                                                     |
+--------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT REGEXP_REPLACE('<br>Hello<br>world','^(<br>)*(.*?)(<br>)*$','\\2');
+--------------------------------------------------------------------+
| REGEXP_REPLACE('<br>Hello<br>world','^(<br>)*(.*?)(<br>)*$','\\2') |
+--------------------------------------------------------------------+
| Hello<br>world                                                     |
+--------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT REGEXP_REPLACE('Hello<br>world','^(<br>)*(.*?)(<br>)*$','\\2');
+----------------------------------------------------------------+
| REGEXP_REPLACE('Hello<br>world','^(<br>)*(.*?)(<br>)*$','\\2') |
+----------------------------------------------------------------+
| Hello<br>world                                                 |
+----------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]>

Answer 2

SELECT REPLACE('content','<br>','') FROM MYTB
WHERE content LIKE '<br>%'

Answer 3

您可以在MYSQL中使用REGEXP：

SELECT REPLACE('content','<br>','') as content  FROM table WHERE content REGEXP '^<br>'