Remove part of text inside column in MYSQL

3

I need to remove an HTML code from within a tab in the database.

In the case the table name is OBS, and inside it has normal text inside 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 would like to remove all of this p with a space. Keep the content within p as long as it is not to .

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

    
asked by anonymous 23.05.2018 / 20:09

4 answers

4

Given the response from @Diego (which was the one that most approached my understanding ) and the comment about the tag has a variable value, it would use the following code:

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

With this, obs would be "zeroed" only when its content was related to tag <p> .

    
23.05.2018 / 20:28
4

If it is a fixed text excerpt:

If it is purely text replacement it will be the same problem as I I replied here .

Use Replace to replace this part of content.

It 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 is 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

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

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

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

You can do this from regular expressions ( REGEXP ). But for specific replies, we would have to know what the basic code structure is looking for.

I hope I have helped.

    
23.05.2018 / 20:19
3

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

update PRODUTOS set OBS=' '

This will delete all the values in the OBS column and change to a ' ' space, if you want to change nothing , use ''

update PRODUTOS set OBS=''
    
23.05.2018 / 20:16
2

Use the Replace function:

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 replace function is responsible for replacing one part of a string with another.

If this is the entire 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>";
    
23.05.2018 / 20:14