locked
TSQL: is it possible to Pass recordset to LIKE? RRS feed

  • Question

  • User1449393024 posted

    Hi there.

    I have a recordset of names of persons.

    Is i possible to check names with another table in like?

    FOR EXAMPLE:

    SELECT * FROM TABLE1

    WHERE Name LIKE (SELECT Name FROM Table2)

    Sunday, April 14, 2013 3:46 PM

Answers

  • User1124521738 posted

    just do a not exists - (using exists in the where will prevent row duplication in the results )

    SELECT     t1.*
    FROM         Table1 AS t1
    where not exists(select t2.Name
                    from Table2 AS t2
                    where t1.Name like '%'+t2.Name+'%')

    if you have particularly large datasets, you can do an inverse operation by finding the places that do match and then return everything but as not exists can perform less well on large data.

    SELECT     t1.*
    FROM         Table1 AS t1 
    where t1.TableID not in (
    	SELECT     t1.TableID
    	FROM         Table1 AS t1 
    	where exists(select t2.TitleBar
    			from Table2 AS t2
    			where t1.Name like '%'+t2.Name+'%')
    )



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, April 14, 2013 4:17 PM

All replies

  • User1124521738 posted

    more likely do an iner join on the like or an exists subquery with the like in the where clause

    SELECT     t1.*, t2.Name
    FROM         Table1 AS t1
    INNER JOIN Table2 AS t2 ON t1.Name like '%'+t2.Name+'%'

    SELECT     t1.*
    FROM         Table1 AS t1
    where exists(select t2.Name
    		from Table2 AS t2
    		where t1.Name like '%'+t2.Name+'%')

    Sunday, April 14, 2013 3:54 PM
  • User1508394307 posted
    SELECT * FROM table1, table2 WHERE table1.name LIKE table2.name + '%'
    Sunday, April 14, 2013 4:01 PM
  • User1449393024 posted

    Thanks for your replys but what I have forgot to say is that I was the result which is not in the recordset.

    Only records which not contain any of values of another table

    Sunday, April 14, 2013 4:13 PM
  • User1508394307 posted

    Not contain? Use NOT

    SELECT * FROM table1, table2 WHERE NOT table1.name LIKE table2.name + '%'

    And I'm not sure if I got your question regarding recordset. Maybe you can explain it with some example.

    Sunday, April 14, 2013 4:15 PM
  • User1124521738 posted

    just do a not exists - (using exists in the where will prevent row duplication in the results )

    SELECT     t1.*
    FROM         Table1 AS t1
    where not exists(select t2.Name
                    from Table2 AS t2
                    where t1.Name like '%'+t2.Name+'%')

    if you have particularly large datasets, you can do an inverse operation by finding the places that do match and then return everything but as not exists can perform less well on large data.

    SELECT     t1.*
    FROM         Table1 AS t1 
    where t1.TableID not in (
    	SELECT     t1.TableID
    	FROM         Table1 AS t1 
    	where exists(select t2.TitleBar
    			from Table2 AS t2
    			where t1.Name like '%'+t2.Name+'%')
    )



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, April 14, 2013 4:17 PM