Search for text between delimiters in MySQL

Question

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

e.g. find foo in the following

test '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

Answer 1

A combination of LOCATE and MID would probably do the trick.

If the value "test 'esf :foo: bar" was in the field fooField:

MID( fooField, LOCATE('foo', fooField), 3);

Answer 2

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.

Answer 3

select mid(col from locate(':',col) + 1 for 
locate(':',col,locate(':',col)+1)-locate(':',col) - 1 ) 
from table where col rlike ':.*:';

Answer 4

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

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

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.

Answer 5

With only one set of delimeters, the following should work:

SUBSTR(
    SUBSTR(fooField,LOCATE(':',fooField)+1),
    1,
    LOCATE(':',SUBSTR(fooField,LOCATE(':',fooField)+1))-1
 )

Answer 6

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)";