The query I'm trying to write (using MySQL Workbench) needs to search a text field and return all the occurrences of a "placeholder" substring within the text field.
The text I'm searching is actually a document containing placeholders and I would like to see a list of all the placeholders in it. Each placeholder has a different value, but it is demarcated by special characters(e.g. |^ ... ^|).
The problem I'm having is that I cannot do looping or recursion in Workbench to search through the text field, so I need some other method to search it. All the MySQL functions that I have tried (e.g. locate() ) only return the first occurrence of the substring (or it's position).
If my text field is:
"blah blah blah |^123^| blah blah blah |^456^| blah blah blah |^789^| blah"
I would like to see:
Placeholder
----------------
123
456
789
As per my comment, MySQL is not best suited to this sort of task. That said, here's something to think about...
SET @string = "blah blah blah |^123^| blah blah blah |^456^| blah blah blah |^789^| blah";
SELECT * FROM ints;
+---+
| i |
+---+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
+---+
SELECT DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(@string,'\|\^',i+1),'\|\^',-1),'^|',1) x
FROM ints i;
+-----------------+
| x |
+-----------------+
| blah blah blah |
| 123 |
| 456 |
| 789 |
+-----------------+