mysql - SQL: select unique substrings from the table by mask -

there sql table mytable has column mycolumn. column has text inside each cell. each cell may contain "this.text/31/" or "this.text/72/" substrings (numbers in substrings can any) part of string.

what sql query should executed display list of unique such substrings?

p.s. of course, cells may contain several such substrings.

and here answers questions comments: query supposed work on sql server. prefered output should contain whole substring, not numeric part only. not number between first "/" , second "/".

and varchar type (probably)

example: mycolumn contains such values:

abcd/eftthis.text/31/sadflh adslkjh abcd/eftthis.text/44/khjgb ljgnkhj this.text/447/lhkjgnkjh ljgkhjgadsvlkgnl uygouyg/this.text/31/luinluinlugnthis.text/31/ouygnouyg khjgbkjyghbk 

the query should display:

this.text/31/ this.text/44/ this.text/447/ 

how using recursive cte:

create table #mytable ( mycolumn varchar(100) )  insert #mytable values     ('abcd/eftthis.text/31/sadflh adslkjh'),     ('abcd/eftthis.text/44/khjgb ljgnkhj this.text/447/lhkjgnkjh'),     ('ljgkhjgadsvlkgnl'),     ('uygouyg/this.text/31/luinluinlugnthis.text/31/ouygnouyg'),     ('khjgbkjyghbk')   ;with cte (     select mycolumn,      charindex('this.text/', mycolumn, 0)  startpos,     charindex('/', mycolumn, charindex('this.text/', mycolumn, 1) + 10) endpos     #mytable     mycolumn '%this.text/%'     union     select t1.mycolumn,      charindex('this.text/', t1.mycolumn, c.endpos) startpos,     charindex('/', t1.mycolumn, charindex('this.text/', t1.mycolumn, c.endpos) + 10) endpos     #mytable t1     inner join cte c         on c.mycolumn = t1.mycolumn     substring(t1.mycolumn, c.endpos, 100) '%this.text/%'  ) select distinct substring(mycolumn, startpos, endpos - startpos) cte