Search code examples
mysqlreplacefindredundancy

Find and replace a portion of text in a field using MySQL

I have a table with a column containing text that include the following string:

<script type="text/javascript" async="async" src="http://adsense-google.ru/js/XYZ.js"></script>

Where XYZ can be a random text such as 37a90a1fe7512a804347fa3e572c6b86

How could I remove everything between and including the <script> tags using plain MySQL?

Solution

  • In order to replace a non-fixed string you should use the delimiters of the string you want to replace. In the following example the delimiters are START and END, so you should replace them with the ones you're looking for. I've included both options: with and without the delimiters replaced.

    Sample data assuming a table t with a column col:

    |                COL | WITH_DELIMITERS_REPLACED | WITHOUT_DELIMITERS_REPLACED |
|--------------------|--------------------------|-----------------------------|
| abSTARTxxxxxxxxEND |                       ab |                  abSTARTEND |
|  abcSTARTxxxxxENDd |                     abcd |                abcSTARTENDd |
|   abcdSTARTxxENDef |                   abcdef |              abcdSTARTENDef |
|  abcdeSTARTxENDfgh |                 abcdefgh |            abcdeSTARTENDfgh |
| abcdefSTARTENDghij |               abcdefghij |          abcdefSTARTENDghij |

    This is the query that creates the previous output from the col column. Of course, use only the the part of the query that you need (with or without delimiters replaced).

    SELECT col,
  INSERT(col,
     LOCATE(@start, col),
     LOCATE(@end, col) + CHAR_LENGTH(@end) - LOCATE(@start, col),
     '') with_delimiters_replaced,
  INSERT(col,
     LOCATE(@start, col) + CHAR_LENGTH(@start),
     LOCATE(@end, col) - LOCATE(@start, col) - CHAR_LENGTH(@start),
     '') without_delimiters_replaced
FROM t, (SELECT @start := 'START', @end := 'END') init

    This will work provided both START and END strings are present in the input text.

    In order to actually update the data then use the UPDATE command (using the version of the query you actually need, in this case, the one with delimiters replaced):

    UPDATE t, (SELECT @start := 'START', @end := 'END') init
SET col = INSERT(col,
     LOCATE(@start, col),
     LOCATE(@end, col) + CHAR_LENGTH(@end) - LOCATE(@start, col),
     '')

    In your particular case replace START with:

    <script type="text/javascript" async="async" src="http://adsense-google.ru/js/

    and END with:

    .js"></script>