Using select from a table variable in a LIKE condition

Question

I have a server log file that I am trying to pare down for easier inspection. The table I am searching through is from just doing a bulk insert into a single column table named Spam.LogData. One big part of doing this is removing activity of legitimate users. I already have a statement that can extract the session IDs that need to be removed by parsing a particular line from the log. It's the only line that always has the session ID and email address in the same place. I can then read the session IDs into a pretty simple table variable.

declare @sessionid table (sesID int)

The problem is creating a like statement using the data from this table to find & remove the necessary lines. I thought this statement would allow me to select out the lines associated with these session IDs and by flipping the comment from the delete to the select, remove them.

--delete
select *
from Spam.LogData
where data like '%' + (select 'session ' + cast(sesID as nvarchar) from @sessionid) + '%'

However I get the error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

What's the best way to do this?

Answer 1

Perhaps you could use a JOIN instead of a subquery:

--delete LD
select LD.*
from Spam.LogData as LD
inner join @sessionid as S on LD.data like '%session ' + cast(S.sesID as nvarchar) + '%'

Answer 2

Try using CTE.

I'm not sure how you are getting the list of session ID's to Filter so this is rather general.

declare @yourVar varchar(64) = '12345678';

with CTE as (select sessionID from SessionTable
  where sessionID like '%'+ @yourVar + '%')
select * from Spam.LogData
--delete Spam.LogData
  where [data] in (select sessionID from CTE)

UPDATED: Ryan, if I understand your comment, your data looks similar to this.

declare @table table(val int,  tID int identity)
insert into @table values( 1)
insert into @table values( 2)
insert into @table values( 5)

You need to find records with the int values as part of a string in a log table. For example: session1, session2, session5 but not session3 or session4

create table logData (sessionID varchar(50), misc varchar(24), id int identity )

insert into logData (sessionID, misc) values('session1', 'aaa')
insert into logData (sessionID, misc) values('session1','bbb')
insert into logData (sessionID, misc) values('session2', 'ccc')
insert into logData (sessionID, misc) values('session3', 'aaa')
insert into logData (sessionID, misc) values('session4','bbb')
insert into logData (sessionID, misc) values('session4', 'ccc')
insert into logData (sessionID, misc) values('session5', 'aaa')
insert into logData (sessionID, misc) values('session5','bbb')

Not elegant but it will get the job done. If this doesn't help you can try looking at cursors.

Make sure your declared @table with the session value integers has an Identity column.

declare @pos int = 1, @last int, @val varchar(50)
select @last = max(tID) from @table

while ( @pos <= @last)
  begin
    select @val = cast(val as varchar) from @table where tID = @pos
    select * from logData where sessionID  like 'session%' + @val 
    set @pos = @pos+ 1
  end

-- drop table logData