Bing Bing - 1 month ago 7
MySQL Question

MySQL Substring between two DIFFERENT strings where the second needle comes AFTER the first

I have to extract certain data from a MySQL column. The table looks like so:

+----+---------------------+------------------------+
| id | time | data |
+----+---------------------+------------------------+
| 1 | 2016-10-28 00:12:01 | a Q1!! AF3 !! ext!! z |
| 2 | 2016-10-28 02:19:02 | z !!3F2 !AF66-2!! !!a |
| 3 | 2016-10-28 11:35:03 | AF!a !!! pl6 f !!! dd |
+----+---------------------+------------------------+


I want to grab the string from column
data
between the characters
AF
and the NEXT occurrence of
!!
So ideally the query
SELECT
id
,[something] AS x FROM tbl
would result in:

+----+------+
| id | x |
+----+------+
| 1 | 3 |
| 2 | 66-2 |
| 3 | !a |
+----+------+


Thoughts on how to do this? All the other questions I see don't quite relate, as they don't deal with finding the first occurrence of the second needle (
!!
) AFTER the first needle (
AF
).

Answer

There may be faster ways to do this but this is a good start:

 select substring_index(substring_index(data, 'AF', -1), '!!', 1)
Comments