CODEWITHSUNDEEP
mysqlreplace
akrisanov
akrisanov

Reputation: 3214

Replacing the first <br /> tag in MySQL column

There is some text column containing some value. E.g.

<ul>
<li>bla bla&nbsp;bla bla bla&nbsp;bla bla bla.</li>
<li>bla bla&nbsp;bla bla bla&nbsp;bla bla bla.</li>
</ul>

or

<br />• text text text.
<br />• text text text.
<br />

How do I remove only the first <br/> tag in each record that begins with it?

Upvotes: 1

Views: 979

Answers (3)

The Scrum Meister
The Scrum Meister

Reputation: 30131

If you only want to remove the 1st <br /> if the record starts with <br />:

UPDATE table
SET field = SUBSTRING(field, 7)
WHERE LEFT(field, 6) = '<br />'

Upvotes: 1

xzyfer
xzyfer

Reputation: 14135

You can just use the mysql trim function:

TRIM(LEADING '<br />' FROM column_name)

example:

SELECT TRIM(LEADING '<br />' FROM doyouknow) as doyouknow FROM actors;

This will remove the "<br />" from the start of doyouknow if it is there or do nothing. This will not change the data in your database like an update query.

Upvotes: 1

RichardTheKiwi
RichardTheKiwi

Reputation: 107776

SELECT
  CASE WHEN doyouknow LIKE '<br />%' THEN SUBSTRING(doyouknow, 7) ELSE doyouknow END,
  othercol1,
  othercol
FROM..

If you need to update the data in the table, use this

UPDATE TBL
SET doyouknow =
  CASE WHEN doyouknow LIKE '<br />%' THEN SUBSTRING(doyouknow, 7) ELSE doyouknow END,

Upvotes: 0

Related Questions