My SQL removes all occurrences. How to remove only the last <br> ?

 SELECT REPLACE( 'aaa bbb <br> ccc <br> ddd', '<br>', '' );

    1 answer 1

    SQL is not intended for such tasks as text conversion and, moreover, HTML. Choose the right tools for solving problems. But if you really want it all, then of course you can also on MySQL, but it turns out creepy:

     select concat( substr(STR,1,length(STR)-length(substring_index(STR, '<br>', -1))-length('<br>')), substring_index(STR, '<br>', -1) ) from ( SELECT 'aaa bbb <br> ccc <br> ddd' as STR ) A
    • And how to make it find all br (<br>, <br/>, <br />, <br />)? - user216109
    • @Sergey In advance, replace all the spellings with one. Or write some kind of wild case / if which looks at the positions of each of the options and gives the maximum position at the output. Or patch MySQL server, add regular expression support github.com/hholzgra/mysql-udf-regexp - Mike
    • I have designed my question , please, if you can do it then help - user216109
    • @Sergey If this is a one-time job, you do not want to export the entire table to a text file, replace everything with any means working with text (sed, perl, etc.) and dump the dump back to the database. It will be faster. Well, or in any language with the support of regulars, read and replace line by line, 1000 records or something. And if in the database you do not hook up the module for regulars, then write a finite state machine that will scroll character by character - Mike
    • They require SQL from me. That is, I think that they will repeat such errors all the same - user216109