两个字符串之间的 MySQL 子字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28431268/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL substring between two strings
提问by Haohmaru
I need a hand to solve a problem with my column field. I need to extract the string in between these two different "patterns" of strings for example:
我需要一只手来解决我的列字段的问题。我需要在这两个不同的字符串“模式”之间提取字符串，例如：
[...string] contract= 1234567890123350566076070666 issued= [string ...]
[...字符串] 合约= 1234567890123350566076070666 发出= [字符串...]
I want to extract the string in between 'contract=' and 'issued='
我想提取 'contract=' 和 'issued=' 之间的字符串
At the present moment I'm using
目前我正在使用
SELECT substring(substring_index(licence_key,'contract=',-1),1,40) FROM table
The problem is that this string in between doesn't have always 40 characters so it's not fixed length and so the data that comes before and after that. It's a volatile data.
问题是中间的这个字符串并不总是有 40 个字符，所以它不是固定长度，所以它之前和之后的数据。这是一个不稳定的数据。
Do you known how I can handle that?
你知道我怎么处理吗？
回答by Gordon Linoff
Just use substring_index()twice:
只需使用substring_index()两次：
SELECT substring_index(substring_index(licence_key, 'contract=', -1),
'issued=', 1)
FROM table;
回答by Gajanand Pandey
If this string does not match then give the total result.
如果此字符串不匹配，则给出总结果。
If you want to replace then you can use like this.
如果你想更换，那么你可以像这样使用。
UPDATE questions set question= REPLACE(question, '<xml></xml>', '') WHERE question like '%<xml>%';
UPDATE questions set question= REPLACE(question, substring_index(substring_index(question, '<xml>', -1), '</xml>', 1), '') WHERE question like '%<xml>%';
