使用MySQL查找并替换字段中的部分文本 [英] Find and replace a portion of text in a field using MySQL
问题描述
< script type =text / javascriptasync =asyncsrc =http://adsense-google.ru/js/XYZ.js>< / script>
其中 XYZ
可以是一个随机文本作为 37a90a1fe7512a804347fa3e572c6b86
如何删除< script> / code>标签使用普通的MySQL?
为了替换一个不固定的字符串,你应该使用分隔符你想要替换的字符串。在以下示例中,分隔符是 START
和 END
,因此您应该将其替换为您正在查找的分隔符。我已经包含了两个选项:有和没有分隔符被替换。
假设一个表 t
列 col
:
COL | WITH_DELIMITERS_REPLACED | WITHOUT_DELIMITERS_REPLACED |
| -------------------- | ------------------------ - | ----------------------------- |
| abSTARTxxxxxxxxEND | ab | abSTARTEND |
| abcSTARTxxxxxENDd | abcd | abcSTARTENDd |
| abcdSTARTxxENDef | abcdef | abcdSTARTENDef |
| abcdeSTARTxENDfgh | abcdefgh | abcdeSTARTENDfgh |
| abcdefSTARTENDghij | abcdefghij | abcdefSTARTENDghij |
这是从 这样可以提供 为了实际更新数据,请使用 在您的特定情况下,将 和 I have a table with a column containing text that include the following string: Where How could I remove everything between and including the 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 Sample data assuming a table This is the query that creates the previous output from the This will work provided both In order to actually update the data then use the In your particular case replace and
这篇关于使用MySQL查找并替换字段中的部分文本的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋! col $创建前一个输出的查询c $ c>栏。当然,只使用你需要的查询部分(带或不带分隔符)。
$ p $ SELECT col,
INSERT(col,
LOCATE(@start,col),
LOCATE(@end,col)+ CHAR_LENGTH(@end) - LOCATE(@start,col),
' ')with_delimiters_replaced,
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
START
和 END $ c
UPDATE
命令(使用您实际需要的查询版本,在这种情况下,用分隔符替换):
UPDATE t, (SELECT @start:='START',@end:='END')init
SET col = INSERT(col,
LOCATE(@start,col),
LOCATE(@end,col)+ CHAR_LENGTH(@end) - LOCATE(@start,col),
'' )
START
替换为:
< script type =text / javascriptasync =asyncsrc =http:// adsense-google .ru / js /
END
with :
.js>< / script>
<script type="text/javascript" async="async" src="http://adsense-google.ru/js/XYZ.js"></script>
XYZ
can be a random text such as 37a90a1fe7512a804347fa3e572c6b86
<script>
tags using plain MySQL?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.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 |
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
START
and END
strings are present in the input text.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),
'')
START
with:<script type="text/javascript" async="async" src="http://adsense-google.ru/js/
END
with:.js"></script>