在MySQL中的定界符之间搜索文本 [英] Search for text between delimiters in MySQL

问题描述

我正在尝试提取定界符之间的列的特定部分.

I am trying to extract a certain part of a column that is between delimiters.

例如在下面找到foo

e.g. find foo in the following

测试'esf:foo:bar

test 'esf :foo: bar

所以在上面我想返回foo，但是所有的regexp函数只返回true | false， 有没有办法在MySQL中做到这一点

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

推荐答案

芽，芽:

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.

通过执行LOCATE，我们可以找到第一个':'.要找到最后一个':'，没有反向LOCATE，因此我们必须通过执行LOCATE(':'，REVERSE(column))来手动完成.

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)).

具有第一个':'的索引，从最后一个':'到字符串末尾的字符数，以及CHAR_LENGTH(请勿为此使用LENGTH() )，我们可以使用一些数学运算来发现':'的两个实例之间的字符串长度.

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 ':'.

这样，我们可以执行SUBSTR并动态抽取两个':'之间的字符.

This way we can peform a SUBSTR and dynamically pluck out the characters between the two ':'.

再说一次，这很麻烦，但对每个人来说都是这样.

Again, it's gross, but to each his own.

这篇关于在MySQL中的定界符之间搜索文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！