CODEWITHSUNDEEP
mysql
anon355079
anon355079

Reputation:

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

I have rows such as

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:

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?

Upvotes: 3

Views: 2331

Answers (3)

Sathish kumar
Sathish kumar

Reputation: 19

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

Upvotes: 0

Michael Eakins
Michael Eakins

Reputation: 4179

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.

Upvotes: 1

Michael Eakins
Michael Eakins

Reputation: 4179

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]%'

Upvotes: 1

Related Questions