使用 MySQL 查找和替换字段中的部分文本 [英] 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?

解决方案

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> 

这篇关于使用 MySQL 查找和替换字段中的部分文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！