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

blpfk2vs  于 2021-06-20  发布在  Mysql
关注(0)|答案(6)|浏览(304)

这是我的table:

-- 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)

它只是移除了周围的空间。

6ss1mwsb

6ss1mwsb1#

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

gg58donl2#

您可以使用如下查询:

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)]>
jv4diomz

jv4diomz3#

SELECT CASE WHEN substr(content,1,4) ='<br>' 
            THEN substr(content,5,len(content))  
            ELSE content 
       END as content
i1icjdpr

i1icjdpr4#

mysql有一个 substring 可使用的功能:

UPDATE mytb SET content = SUBSTRING(content, 5)
WHERE content LIKE '<br>%'
nwlls2ji

nwlls2ji5#

您可以在mysql中使用regexp:

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

dffbzjpn6#

将mysql子字符串与if条件一起使用。

SET content = TRIM
                (IF
                  (SUBSTRING
                     (TRIM(content), 1, 4) = '<br>',
                      SUBSTRING(TRIM(content), 5), content
                  )
                );

相关问题