使用 MySQL 查找和替换字段中的部分文本

本文介绍了使用 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 查找和替换字段中的部分文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！

本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除！

使用 MySQL 查找和替换字段中的部分文本

问题描述

相关文档推荐