CODEWITHSUNDEEP
sqlsql-serversql-server-2012
AJMi
AJMi

Reputation: 33

Passing select result to like operator in SQL

I have results of name in a customer table

I want to find whether this name is present in log table column

My log table changedate column will record name and also id, I want to find only name

select top 100 *
from LogDataTable
where ChangeData like '%'+'select name from customer'+ '%'

But I'm not getting results with this query, any changes I have to do? Can it done?

Upvotes: 0

Views: 749

Answers (1)

Dale K
Dale K

Reputation: 27201

You can use a join to compare data the way you are asking e.g.

select top 100 L.*
from dbo.LogDataTable L
inner join dbo.Customer C on L.ChangeData like '%' + C.[Name] + '%';

Its not going to perform very well though, as it has to do a table scan of Customer for every row in LogDataTable.

If you were able to ensure that the Customer Name was always the first part of the ChangeData you could use like C.[Name] + '%' and then benefit from indexes.

To solve your collation error, re-collate of one of the columns in your query. I recommend changing C.[Name] since that is already the subject of a table scan. But it depends whether you need an "Accent Sensitive" compare or not e.g.

select top 100 L.*
from dbo.LogDataTable L
inner join dbo.Customer C on L.ChangeData like '%' + C.[Name] collate SQL_Latin1_General_CP1_CI_AS + '%';

Upvotes: 5

Related Questions