Multi tool use

How to remove <br>s that are in the beginning of the string?

Here is my table:

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


I need to remove all <br>s that are in the beginning of the string. I can specify it using ^ in PHP regex. How can I do the same in MySQL?

<br>

^

update mytb set content = trim(content)


It just removes the surrounded spaces.

set content = REPLACE(content, '<br>', '')

<br>

<br>

6 Answers
6

You can use a query like this:

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


Sample

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


You can use REGEXP in MYSQL:

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


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


replace()

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


Use MYSQL substring with if condition.

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


MYSQL has a substring function that can be used:

substring

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


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.