[ACCEPTED]-Search for text between delimiters in MySQL-mysql

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 14 doing this, but this will do the trick.

By 13 performing a LOCATE, we can find the first 12 ':'. To find the last ':', there's no reverse 11 LOCATE, so we have to do it manually by 10 performing a LOCATE(':', REVERSE(column)).

With 9 the index of the first ':', the number of 8 chars from the last ':' to the end of the 7 string, and the CHAR_LENGTH (don't use LENGTH() for this), we can use 6 a little math to discover the length of 5 the string between the two instances of 4 ':'.

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

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

This should work if the two delimiters only 2 appear twice in your column. I am doing 1 something similar...

substring_index(substring_index(column,':',-2),':',1)

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

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

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

I don't know if you have this kind of authority, but 3 if you have to do queries like this it might 2 be time to renormalize your tables, and 1 have these values in a lookup table.

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

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

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 2 number 3

mid(col, locate('?m=',col) + 3, locate('&o=',col) - locate('?m=',col) - 3)

the patterns I have used are '?m=' and 1 '&o'.

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

0

If you know the position you want to extract 2 from as opposed to what the data itself 1 is:

$colNumber = 2; //2nd position
$sql = "REPLACE(SUBSTRING(SUBSTRING_INDEX(fooField, ':', $colNumber),
                             LENGTH(SUBSTRING_INDEX(fooField, 
                                                    ':', 
                                                    $colNumber - 1)) + 1)";

This is what I am extracting from (mainly 5 colon ':' as delimiter but some exceptions), as 4 column theline255 in table loaddata255:

23856.409:0023:trace:message:SPY_EnterMessage (0x2003a) L"{#32769}"      [0081] WM_NCCREATE sent from self wp=00000000 lp=0023f0b0

This 3 is the MySql code (It quickly did what I 2 want, and is straight forward):

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

This one looks elegant to me. Strip all 3 after n-th separator, rotate string, strip 2 everything after 1. separator, rotate back.

select
  reverse(
    substring_index(
      reverse(substring_index(str,separator,substrindex)),
      separator,
      1)
  );

For 1 example:

select
  reverse(
    substring_index(
      reverse(substring_index('www.mysql.com','.',2)),
      '.',
      1
    )
  );