SQL like with two wildcards anywhere in text

Question

Is there a way to create a where statement using like with two words (or more) but they are not in the same order as the words given?

select 
    i.ITEM_NUMBER as Item,
    min(i.ITEM_DESCRIPTION) as Description,
    i.ORGANIZATION_CODE as Org,
    max(m.MANUFACTURER_NAME) as Manufacturer,
    max(m.MANUFACTURERS_PARTNUMBER) as Partnumber
from mis.XXEAM_INVENTORY_REPORT_WITH_LOCATORS_CONSIGNED_MATERIAL_AND_CATEGORIES_TBL i
left outer join 
    mis.XXEAM_MANUFACTURING_PART_NUMBERS_TBL m on i.ITEM_NUMBER = m.ITEM_NUMBER
where 
    i.ITEM_DESCRIPTION like '%ALLEN BRADLEY%PLC%' 
group by i.ORGANIZATION_CODE, i.ITEM_NUMBER

I would like to search the description text above as the '%ALLEN BRADLEY%PLC%' or '%PLC%ALLEN BRADLEY%'. I would like to avoid using OR statements because the dataset is huge and the query can take a long time.

The wildcards can be more than two, I just took those two as an example. In my mind I would for instance prefer to fetch the dataset for the first word and then fetch from that dataset the second word. Perhaps the only way is to select into temp-table.

Answer 1

You can do two LIKE for 'ALLEN BRADLEY' and 'PLC'. It should work for what you want:

SELECT
    i.ITEM_NUMBER AS Item
   ,MIN(i.ITEM_DESCRIPTION) AS Description
   ,i.ORGANIZATION_CODE AS Org
   ,MAX(m.MANUFACTURER_NAME) AS Manufacturer
   ,MAX(m.MANUFACTURERS_PARTNUMBER) AS Partnumber
FROM mis.XXEAM_INVENTORY_REPORT_WITH_LOCATORS_CONSIGNED_MATERIAL_AND_CATEGORIES_TBL i
LEFT OUTER JOIN mis.XXEAM_MANUFACTURING_PART_NUMBERS_TBL m
    ON i.ITEM_NUMBER = m.ITEM_NUMBER
WHERE i.ITEM_DESCRIPTION LIKE '%ALLEN BRADLEY%' 
    and i.ITEM_DESCRIPTION LIKE '%PLC%' 
GROUP BY i.ORGANIZATION_CODE
        ,i.ITEM_NUMBER