在 MySQL 中搜索分隔符之间的文本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/149690/
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
Search for text between delimiters in MySQL
提问by Pete Karl II
I am trying to extract a certain part of a column that is between delimiters.
我正在尝试提取分隔符之间的列的某个部分。
e.g. find foo in the following
例如在下面找到 foo
test 'esf :foo: bar
测试'esf :foo: bar
So in the above I'd want to return foo, but all the regexp functions only return true|false, is there a way to do this in MySQL
所以在上面我想返回 foo，但所有的正则表达式函数只返回 true|false，有没有办法在 MySQL 中做到这一点
回答by Pete Karl II
Here ya go, bud:
来吧，小伙子：
SELECT
SUBSTR(column,
LOCATE(':',column)+1,
(CHAR_LENGTH(column) - LOCATE(':',REVERSE(column)) - LOCATE(':',column)))
FROM table
Yea, no clue why you're doing this, but this will do the trick.
是的，不知道你为什么要这样做，但这会解决问题。
By performing a LOCATE, we can find the first ':'. To find the last ':', there's no reverse LOCATE, so we have to do it manually by performing a LOCATE(':', REVERSE(column)).
通过执行 LOCATE，我们可以找到第一个 ':'。要找到最后一个 ':'，没有反向 LOCATE，因此我们必须通过执行 LOCATE(':', REVERSE(column)) 手动完成。
With the index of the first ':', the number of chars from the last ':' to the end of the string, and the CHAR_LENGTH (don't use LENGTH() for this), we can use a little math to discover the length of the string between the two instances of ':'.
有了第一个 ':' 的索引，从最后一个 ':' 到字符串末尾的字符数，以及 CHAR_LENGTH（不要为此使用 LENGTH()），我们可以用一点数学来发现':' 的两个实例之间的字符串长度。
This way we can peform a SUBSTR and dynamically pluck out the characters between the two ':'.
通过这种方式，我们可以执行 SUBSTR 并动态提取两个 ':' 之间的字符。
Again, it's gross, but to each his own.
再一次，这很恶心，但对每个人来说都是如此。
回答by Danny Z
This should work if the two delimiters only appear twice in your column. I am doing something similar...
如果两个分隔符仅在您的列中出现两次，这应该有效。我正在做类似的事情...
substring_index(substring_index(column,':',-2),':',1)
回答by Mark Biek
回答by Adam Bellaire
I don't know if you have this kind of authority, but if you have to do queries like this it might be time to renormalize your tables, and have these values in a lookup table.
我不知道您是否有这种权限，但是如果您必须执行这样的查询，那么可能是时候重新规范化您的表，并将这些值放在查找表中。
回答by Adam Bellaire
With only one set of delimeters, the following should work:
只有一组分隔符，以下应该工作：
SUBSTR(
SUBSTR(fooField,LOCATE(':',fooField)+1),
1,
LOCATE(':',SUBSTR(fooField,LOCATE(':',fooField)+1))-1
)
回答by hrushikesh
mid(col,
locate('?m=',col) + char_length('?m='),
locate('&o=',col) - locate('?m=',col) - char_length('?m=')
)
A bit compact form by replacing char_length(.)with the number 3
char_length(.)用数字代替有点紧凑的形式3
mid(col, locate('?m=',col) + 3, locate('&o=',col) - locate('?m=',col) - 3)
the patterns I have used are '?m='and '&o'.
我使用的模式是'?m='和'&o'。
回答by brewmanz
This is what I am extracting from (mainly colon ':' as delimiter but some exceptions), as column theline255 in table loaddata255:
这是我从中提取的内容（主要是冒号“：”作为分隔符，但也有一些例外），作为表 loaddata255 中的 theline255 列：
23856.409:0023:trace:message:SPY_EnterMessage (0x2003a) L"{#32769}" [0081] WM_NCCREATE sent from self wp=00000000 lp=0023f0b0
This is the MySql code (It quickly did what I want, and is straight forward):
这是 MySql 代码（它很快完成了我想要的，而且很简单）：
select
time('2000-01-01 00:00:00' + interval substring_index(theline255, '.', 1) second) as hhmmss
, substring_index(substring_index(theline255, ':', 1), '.', -1) as logMilli
, substring_index(substring_index(theline255, ':', 2), ':', -1) as logTid
, substring_index(substring_index(theline255, ':', 3), ':', -1) as logType
, substring_index(substring_index(theline255, ':', 4), ':', -1) as logArea
, substring_index(substring_index(theline255, ' ', 1), ':', -1) as logFunction
, substring(theline255, length(substring_index(theline255, ' ', 1)) + 2) as logText
from loaddata255
and this is the result:
这是结果：
# LogTime, LogTimeMilli, LogTid, LogType, LogArea, LogFunction, LogText
'06:37:36', '409', '0023', 'trace', 'message', 'SPY_EnterMessage', '(0x2003a) L\"{#32769}\" [0081] WM_NCCREATE sent from self wp=00000000 lp=0023f0b0'
回答by mprot
This one looks elegant to me. Strip all after n-th separator, rotate string, strip everything after 1. separator, rotate back.
这个对我来说看起来很优雅。在第 n 个分隔符之后剥离所有内容，旋转字符串，在 1. 分隔符之后剥离所有内容，向后旋转。
select
reverse(
substring_index(
reverse(substring_index(str,separator,substrindex)),
separator,
1)
);
For example:
例如：
select
reverse(
substring_index(
reverse(substring_index('www.mysql.com','.',2)),
'.',
1
)
);
回答by micahwittman
If you know the position you want to extract from as opposed to what the data itself is:
如果您知道要从中提取的位置而不是数据本身：
$colNumber = 2; //2nd position
$sql = "REPLACE(SUBSTRING(SUBSTRING_INDEX(fooField, ':', $colNumber),
LENGTH(SUBSTRING_INDEX(fooField,
':',
$colNumber - 1)) + 1)";
回答by Vinko Vrsalovic
select mid(col from locate(':',col) + 1 for
locate(':',col,locate(':',col)+1)-locate(':',col) - 1 )
from table where col rlike ':.*:';
