18

How would you use 'LIKE' to search in a subquery?

E.g. i've tried doing this, but doesn't work:

SELECT *
FROM mytable
WHERE name
    LIKE '%
        (SELECT name FROM myothertable)
        %'

I have this so far:

SELECT * FROM t1
WHERE t1.name IN (SELECT t2.name FROM t2)
AND (t1.title IN (SELECT t2.title FROM t2)
    OR t1.surname IN (SELECT t2.surname FROM t2))

It's working ok as it returns exact matchs, but it doesn't seem to return my other records that are similar, so I would like to also check that:

t1.title LIKE '%t2.title%' AND t1.surname LIKE '%t2.surname%'

How would i do this?

Improve this question
5
  • How many rows does your myothertable have?
    – zerkms
    Apr 5 '12 at 3:38
  • only two little correction in your code. 1st remove % and like.Second add in after name, full code is in my answer.
    – Ankit Sharma
    Apr 5 '12 at 6:39
  • THANK YOU ALL FOR YOUR SPEEDY RESPONSES SO FAR ^_^. @zerkms - about 10 but this doesn't really affect what i'm trying to do, i just want to compare a few fields.
    – qwerty
    Apr 5 '12 at 7:39
  • @Jason_vorhees - I've edited your response to include what I have tried to do so far. What I want to do now is to try and do a query for LIKE '% %'
    – qwerty
    Apr 5 '12 at 7:46
  • you cannot do like this "LIKE '%(SELECT name FROM myothertable)%'" try IN instead of LIKE.
    – Ankit Sharma
    Apr 5 '12 at 7:49
27

Using a JOIN:

SELECT a.*
  FROM mytable a
  JOIN myothertable b ON a.name LIKE CONCAT('%', b.name, '%')

...but there could be duplicates, if there's more than one match in myothertable for a given mytable record.

Using EXISTS:

SELECT a.*
  FROM mytable a
 WHERE EXISTS (SELECT NULL 
                 FROM myothertable b 
                WHERE a.name LIKE CONCAT('%', b.name, '%'))

Using Full Text Search MATCH (requires myothertable is MyISAM)

SELECT a.*
  FROM mytable a
  JOIN myothertable b ON MATCH(a.name) AGAINST (b.name)
Improve this answer
2
12

For example:

SELECT a_column
FROM   mytable t
WHERE  EXISTS (
           SELECT 1
           FROM   myothertable ot
           WHERE  t.`name` LIKE '%' || ot.`name` || '%');

As far as terminology goes: this is known as a correlated subquery.

Improve this answer
6
2

Just another way:

select a.field, b.code
from table1 a 
inner join (select code from table2 where ....) b on a.field like CONCAT('%', b.code, '%')
Improve this answer
2

this string works fine for me.

"SELECT * FROM table1 WHERE field like CONCAT('%', (SELECT id FROM table2), '%')";
Improve this answer
1
0

Best way would be to create function called NameMatch()

Final Query : 

SELECT * FROM mytable  WHERE dbo.NameMatch(name) = 1

The function would look like : 

create function dbo.NameMatch 
(@_name varchar(100))
returns bit 
as  begin

    declare @res bit 
    if exists (select 1 from myothertable where @_name like '%' + name + '%' )
     set @res = 1
    else set @res  = 0
    return @res

end
Improve this answer
3
0

It Worked FOR ME

SELECT *
FROM mytable
WHERE name
LIKE CONCAT('%',(SELECT name FROM myothertable),'%')
Improve this answer
2
0 
SELECT * 
FROM t1
WHERE t1.name IN (SELECT t2.name FROM t2)
AND (t1.title IN (SELECT t2.title FROM t2)
    OR t1.surname IN (SELECT t2.surname FROM t2))
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.