There is a table with 1000 or more entries. In many of these entries in the text at the end of what that sage left <br> .

Colleagues, I have a code that changes the last entry that I submit to the procedure. In this example, I am trying to remove the last <br> . But the catch is this: it changes by exact coincidence, that is, <br/> in the span. How can you arrange so that it replaces all the br tags?

 DROP PROCEDURE IF EXISTS replaceBr; DELIMITER | CREATE PROCEDURE replaceBr (IN mytext TEXT, IN br VARCHAR (255)) BEGIN SELECT CONCAT( SUBSTR( mytext, 1, LENGTH(mytext) - LENGTH(SUBSTRING_INDEX(mytext, br, - 1)) - LENGTH(br) ), SUBSTRING_INDEX(mytext, br, - 1 ) ) ; END ; | DELIMITER ; CALL replaceBr ('aaa bbb <br> ccc <br > ddd', '<br>') ;

I think you first need to bring mytext to a single standard. That is, replace all matching br to a single <br> . And then my function will work.

In my opinion, the regular form looks like this: "/[\n\r\s]*<br\s*\/?>[\n\r\s]*/" . But how to apply it, how to make it all work?

Thank.

  • There is such a library for searching and replacing based on regular expressions in MySQL , maybe it will do. - Wiktor Stribiżew
  • @Wiktor Stribiżew No, unfortunately it will not work, only SQL is required from me - user216109
  • In the text, extraneous characters < and > i.e. not part of the tags. for example abc <div id="a>x"> (a very strained example of course)? - Mike
  • @Mike Only need br , the rest is not interested - user216109

1 answer 1

In principle, you can run through the string in the loop, since you did the procedure, but I used to do everything in one query. (By the way, your replacement on the entire table can be performed in one query without procedures).

For the query to work, you need a table with sequence numbers (it replaces the loop in the queries, multiplying the records). The numbers should be from 1 to the maximum length of the processed line:

 create table seqnum(X int not null, primary key(X)); insert into seqnum values(1),(2),(3),(4); insert into seqnum select X+4 from seqnum; insert into seqnum select X+8 from seqnum; insert into seqnum select X+16 from seqnum; insert into seqnum select X+32 from seqnum;

Here is an example for replacing on one line:

 select coalesce(concat(substr(STR,1,max(X)-1), substr(STR,locate('>',STR,max(X))+1)),STR) from ( SELECT 'aaa bbb <br> ccc < br/ > ddd' as STR ) A, seqnum S where SX<=length(STR) and substr(STR,SX) REGEXP '^< *br */? *>'

The idea is that MySQL can search by a regular expression, but cannot replace it. We take our string and successively remove characters from it starting from the beginning and check the remaining tail for regular expression. We get the maximum position in the row in which the regular result gave a positive result. Starting from this position, we delete all the text until the next character > . To process an entire row table, add to the internal subquery the retrieval of rows from that table and at the end add a group by STR (preferably by the unique id of the rows of that table).