sql server 2012 - SQL query to select records which must have all values from another table as substring -

i have table this

rules (rulevalue varchar(50)) 

it has values

a1b1c1 a1b1c0 a1b0c0 

there table

input (rulepart varchar(2)) 

it can have values like:

a1 b1 c1 

i want rulevalues rulepart matches anywhere in rulevalue following example hardcoded ruleparts:

select rulevalue rules rules.rulevalue '%a1%' , rules.rulevalue '%b1%' , rules.rulevalue '%c1%' 

with above examples expected result a1b1c1 or b1a1c1 or c1a1b1 etc..

how can this?

i tried use inner join not matches rule parts in every row.

i can achive using creating query dynamically don't want go unless affects query performance.

one approach is:

select r.rulevalue    rules r   join input     on r.rulevalue '%' + i.rulepart + '%'  group r.rulevalue  having count(distinct i.rulepart) = 3     -- or (select count(*) input ) 

update more elegant way using not exists represent all

select *    rules r  not exists      (        select *           input         r.rulevalue not '%'+i.rulepart+'%'      ) 

sql fiddle demo