CODEWITHSUNDEEP
sqlsubquerywildcard
jordan
jordan

Reputation: 3546

SQL Like with a subquery

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?

Upvotes: 27

Views: 83848

Answers (5)

SACHIN SEBASTIAN
SACHIN SEBASTIAN

Reputation: 1

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

Upvotes: 0

cometos
cometos

Reputation: 41

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

Upvotes: 4

Matas Vaitkevicius
Matas Vaitkevicius

Reputation: 61391

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

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1269513

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

Upvotes: 13

Lamak
Lamak

Reputation: 70638

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

Upvotes: 40

Related Questions