Remove text within column in MYSQL

Asked

Viewed 1,474 times

1

I need to remove an HTML code that is inside a tebela in the database.

In case the table name is OBS, and inside it has a normal text within a p and also another p with the following code:

<p><a target="_blank" href="/uploads/docs/58e7a4fd05ca6.pdf "><img alt="" style="width: 175px; height: 45px;" src="/uploads/imagens/59d292393a88f_900x.jpg" /></a></p>

I’d like to remove all that p by a blank space. Keep the content inside p as long as it’s not that to.

The table name is PRODUCTS and the column is OBS. What the syntax would look like?

mysql
  • 1

    You want to be alone with the tags? Erase everything? It wasn’t clear to me

  • 1

    I answered there, but reading the @rLinhares question really raised some doubts: the content within this P is variable in each record (i.e., reference different links and images)? In this column there is a chain of chained elements that include several P and you want to remove only the P more internal?

4 answers

3

If it is a fixed text snippet:

If it’s purely text replacement it’ll be the same problem as me I answered here.

Uses the function Replace to replace that part of the content.

Would look like this:

UPDATE produtos
    SET obs= REPLACE(obs, '<p><a target="_blank" href="/uploads/docs/58e7a4fd05ca6.pdf "><img alt="" style="width: 175px; height: 45px;" src="/uploads/imagens/59d292393a88f_900x.jpg" /></a></p>', '')  
WHERE obs LIKE '%<p><a target="_blank" href="/uploads/docs/58e7a4fd05ca6.pdf "><img alt="" style="width: 175px; height: 45px;" src="/uploads/imagens/59d292393a88f_900x.jpg" /></a></p>%'

If the content of P be variable

You can use a combination of SUBSTRING with INSTR:

UPDATE produtos
    SET obs = SUBSTRING(obs, 0, INSTR(obs,'<p>')) + SUBSTRING(obs, INSTR(obs,'</p>'))
WHERE obs IS NOT NULL AND 
      INSTR(obs, '<p>') >=0 AND
      INSTR(obs, '</p>') >=0

The SUBSTRING in syntax SUBSTRING( str, inicio, quantidade) will return [quantidade] characters from position [inicio] string [str] Already in the syntax SUBSTRING( str, inicio ), the function returns all characters of [str] from the position [inicio].

The function INSTR - INSTR( str, strProcurada) - a value greater than or equal to zero corresponding to the index of the initial position of the string str where the text is found strProcurada.

If there are several P nested in the column and you want to remove a specific

You can replace from regular expressions (REGEXP). But to respond specifically, we would have to know the basic structure of the code you’re looking for.

I hope I’ve helped.

2


Considering the response of @Diego (that was the one that came closest to my understanding) and the comment on the tag have a variable value, use the following code:

UPDATE produtos SET obs = ' ' WHERE obs LIKE '<p>%</p>'

Thereby, obs would be "zeroed" only when its contents referred to tag <p>.

1

Use the function Replace:

UPDATE PRODUTOS SET
    OBS = REPLACE(OBS, "<p><a target=\"_blank\" href=\"/uploads/docs/58e7a4fd05ca6.pdf \"><img alt=\"\" style=\"width: 175px; height: 45px;\" src=\"/uploads/imagens/59d292393a88f_900x.jpg\" /></a></p>", "");

The function replace is responsible for replacing a part of a string by another.

If this is all the content of your field, you can do it as follows:

UPDATE PRODUTOS SET
    OBS = ""
WHERE OBS = "<p><a target=\"_blank\" href=\"/uploads/docs/58e7a4fd05ca6.pdf \"><img alt=\"\" style=\"width: 175px; height: 45px;\" src=\"/uploads/imagens/59d292393a88f_900x.jpg\" /></a></p>";

1

I believe that values should change from record to record, but if it is only to delete the contents of the column, a update sem where serves.

update PRODUTOS set OBS=' '

This will erase all values from the OBS column and switch to a space ' ', if you want to trade for nothingness, uses ''

update PRODUTOS set OBS=''

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.