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?

@TechBytes i specifically stated that i have a list of strings i want to compare item_name to. — jordan, Jun 18 '13 at 20:04

score 38 · Accepted Answer · answered Jun 18 '13 at 20:03

38

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

answered Jun 18 '13 at 20:03

Lamak

69,480
12
108
116

ahhh nice nice, i will try this and let you know ASAP. ty – jordan Jun 18 '13 at 20:04
3

For those using SQL Server... The double pipes appears to be the ANSI-SQL standard but won't work in T-SQL. https://stackoverflow.com/questions/23372550/what-does-sql-select-symbol-mean – ChrisG Dec 20 '18 at 15:51
3

Note that this solution produces duplicates. – Joel Christophel Apr 16 '20 at 17:48

score 12 · Answer 2 · answered Jun 18 '13 at 20:06

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

score 4 · Answer 3 · answered Jun 30 '20 at 11:49

4

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

answered Jun 30 '20 at 11:49

Matas Vaitkevicius

58,075
31
238
265

score 4 · Answer 4 · edited Jul 30 '21 at 13:09

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

edited Jul 30 '21 at 13:09

Toni

1,555
4
15
23

answered Jul 29 '21 at 12:26

cometos

41
2

SQL Like with a subquery

4 Answers4

Linked