CODEWITHSUNDEEP
mysql
TryingToBeABetterProgrammer
TryingToBeABetterProgrammer

Reputation: 15

Distinct Path between 2 chars in Mysql

i have a column "path" - a valid path looks like:
"PAGE:StackOverflow:SystemOfReptation:HowTo"
or
"COOKBOOK:Awesome:Cookie"

I need to Distinct all Names in the first Section.
So i have a List like: 

Sections:  
1. PAGE  
2. COOKBOOK

And i need to Distinct all Names in the second Section of a spiecial first Section.
Like for the "PAGE" first Section: 

Section of "PAGE"

1 .StackOverflow

I have absolutely no idea how i can do this is there something like Regex in Mysql i can use for this?

Thanks for Your Help

Upvotes: 0

Views: 38

Answers (1)

senape
senape

Reputation: 342

To obtain the distinct list of the first section, you can use the MySQL functions substring_index

SELECT DISTINCT(SUBSTRING_INDEX(PATH, ':', 1)) SECTIONS
FROM MY_TABLE

This will return the distinct list of everything behind the ":". Please note that if the PATH has no ":", it will be returned entirely. If you need, you can add a where clause such as:

WHERE INSTR(PATH, ":") <> 0

instr() function will return the position of the first occurrence.

Finally, you can use the above query as a subquery to be joined on the same table to extract the second level of your path.

Upvotes: 1

Related Questions