SQL Like with a subquery

For MSSql Server above does not fly

Use

select *
from item I
where exists (select 1
          from equipment_type
          where i.item_name like (SELECT CONCAT('%',equipment_type,'%')) 
         )

If you don't want to worry about duplicates and don't care which one matches, then switch to using exists:

select i.*
from item i
where exists (select 1
              from equipment_type
              where i.item_name like '%'||equipment_type||'%'
             )

You can use CONCAT and insert the subquery:

SELECT * FROM item WHERE  item_name LIKE  
CONCAT('%', (
    SELECT equipment_type
    FROM equipment_type
    GROUP BY equipment_type), '%'
)

You can use an INNER JOIN:

SELECT I.* 
FROM item I
INNER JOIN (SELECT equipment_type 
            FROM equipment_type 
            GROUP BY equipment_type) E
    ON I.item_name LIKE '%' || E.equipment_type || '%'