Search for dimensions members in MDX using inStr()

Sometimes it might be handy to search dimensions members in MDX. The MDX below will show you an example on how to get all products that contains the name ‘Bike’

SELECT [Measures].[Sales Amount] on 0,
	Instr( [Product].[Product].currentmember.Properties( 'Member_Caption' ), 'Bike' )  > 0  
) on 1
from [Adventure Works]

And here is some other examples

List products that does not contain the word ‘Bike’

Instr( [Product].[Product].currentmember.Properties( 'Member_Caption' ), 'Bike' )  = 0  

List products that is named ‘Bike Wash – Dissolver’

Instr( [Product].[Product].currentmember.Properties( 'Member_Caption' ), 
'Bike Wash - Dissolver' )  = 1