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

Question

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?

update mytb set content = trim(content)

It just removes the surrounded spaces.

Do you want to remove `
` only at the start of the string or all `
`'s in the string? — Fred, Jul 02 '18 at 08:58

saravanatn · Answer 1 · 2018-07-02T08:59:56.807

0

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

edited Jul 02 '18 at 08:59

answered Jul 02 '18 at 08:46

saravanatn

630
5
9

2

He said starting
not all
– Daniel E. Jul 02 '18 at 08:48
`replace()` will replace _all_ occurences in the string. I.e. it will replace both '
' from the record with id=4 – Terje D. Jul 02 '18 at 09:31

score 0 · Answer 2 · answered Jul 02 '18 at 08:55

0

You can use REGEXP in MYSQL:

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

answered Jul 02 '18 at 08:55

HamzaNig

1,019
1
10
33

score 0 · Accepted Answer · answered Jul 02 '18 at 10:13

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

Mariadb here and he asks for mysql. – Daniel E. Jul 02 '18 at 14:42 — Daniel E., Jul 02 '18 at 14:42

score -1 · Answer 4 · edited Jul 02 '18 at 09:31

-1

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

edited Jul 02 '18 at 09:31

Anshuman Jaiswal

5,352
1
29
46

answered Jul 02 '18 at 08:53

saravanatn

630
5
9

score -1 · Answer 5 · edited Jul 02 '18 at 09:38

-1

Use MYSQL substring with if condition.

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

edited Jul 02 '18 at 09:38

Jithin Scaria

1,271
1
15
26

answered Jul 02 '18 at 09:04

Irvin

1
1

score -2 · Answer 6 · answered Jul 02 '18 at 08:53

-2

MYSQL has a substring function that can be used:

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

answered Jul 02 '18 at 08:53

Terje D.

6,250
1
22
30

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

6 Answers6

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