Select a distinct list of substrings from one of the fields in a MySQL table

Question

I have rows such as

• [ISSUE] This is a sample issue
• [WEBSITE] A bug in the website
• Note that this row doesn't contain anything inside square brackets
• [WEBSITE] Another bug in the website
• [TRACKER] It is not live!
• Some rows will have them in the [middle] but I don't want them

etc..

Assume the field is called "Title" and the table is called "Issues". All items in that column start with a string within square brackets. There is no definitive list of what words can come inside the square brackets. no restriction on the length also. I need not get it if the field doesn't start with such a string.

I want to get the word inside the pair of square brackets and get a unique list of those words.

I want to select a distinct list of all the [XYZ]s. For example, in the list above, the query should return the following list of strings:

• ISSUE
• WEBSITE
• TRACKER

It is a combination of substring logic, and distinct query. The logic might go like Find the index of the first [ and the first ] and get the string between that and from the list, make a unique list. I am finding it nearly impossible to write it in a single MySQL query. What is the query that will get that list?

Answer 1

If I understand correctly, you have a table which contains a field named Title. The title may start with the string you are looking for and you only want it if it starts with the string. You also want the result to be distinct.

This query will do that.

SELECT Issue, Website, Tracker FROM `Issues` Where distinct(Title) LIKE '[SOMETHING]%'

Answer 2

In that case I would try:

select distinct `Match` from (SELECT MID(Title,instr(Title,'[') + 1,instr(Title,']') - instr(Title,'[') -1) As 'New_Title'
FROM Issues u Where instr(Title,'[') > 0 AND instr(Title,']') > instr((Title),'[')) tbl

Sorry this took so long, I had to figure out how to return the string.

Answer 3

SELECT distinct (case when title REGEXP '^[[]' then concat(substring_index(title, ']', 1),']') end ) as title FROM test.issue i where title REGEXP '^[[]';