SQL Like with a subquery

Question

How can i make this work?

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

The inner sub query returns a list of strings like 'The' 'test' 'another' and i want to select all items from the item table where the item_name is similar to the sub queries return values. I need to have the wild cards.

Is there an alternative where i can use wildcards but use the IN sql command instead?

Lues

Don't you have ID to do this?? – Haggis

@Haggis What do you mean? – Lues

@Haggis i specifically stated that i have a list of strings i want to compare item_name to. – Lues

Answer 1

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 || '%'

Chantellechanter

ahhh nice nice, i will try this and let you know ASAP. ty – Lues

For those using SQL Server... The double pipes appears to be the ANSI-SQL standard but won't work in T-SQL. #23373050 – Entrance

Note that this solution produces duplicates. – Philosophism

Answer 2

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||'%'
             )

Keratoplasty

Answer 3

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,'%')) 
         )

Ignacia

Answer 4

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), '%'
)

Divalent

Answer 5

SELECT * 
FROM   item 
WHERE  item_name LIKE ANY  
                      (SELECT '%'|| equipment_type || '%' 
                          FROM   equipment_type 
                          GROUP  BY equipment_type)

Choochoo

Please explain the effect of what you propose and why it helps to solve the problem. Try for How to Answer. – Xenophobe

In addition to @Yunnosch’s question, this question already has a few answers—including one that has been extensively validated by the community. Are you certain your approach hasn’t been given previously? If so, it would be useful to explain how your approach is different, under what circumstances your approach might be preferred, and/or why you think the previous answers aren’t sufficient. – Feinleib