CODEWITHSUNDEEP
sqlsql-server
solarissf
solarissf

Reputation: 1277

sql compare tables using like

I am trying to compare two tables in sql using the LIKE command in another table.

Table1

accountNumber     description
12345             wires sent out

Table2

keywords
wire
fund
fee

so I am trying to say, give me all the records in table1, that table1.description are LIKE records in table2.keywords

does this make sense? then I am trying to do the reverse... give me all records in table1, that are NOT LIKE table2.keywards.

I have tried this line but it's not working

left join dbo.cashkeywords cashkeyTable on cashkeyTable.keyword like xxxTable.PSDSC1

Upvotes: 0

Views: 95

Answers (2)

cloudsafe
cloudsafe

Reputation: 2504

Use Cross Apply:

declare @Table1 table ([accountNumber] int, [description] nvarchar(100))
insert into @Table1 values (12345, 'wires sent out')

declare @Table2 table ([keywords] nvarchar(100))
insert into @Table2 values 
('wire')
,('fund')
,('fee')

select distinct *
from @Table1 t1
cross apply @Table2 t2
where t1.description like '%' + t2.keywords + '%'

For single returns:

select distinct t1.*
from @Table1 t1
cross apply @Table2 t2
where t1.description like '%' + t2.keywords + '%'

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Presumably, you need wildcards. In standard SQL, this would look like:

left join
dbo.cashkeywords cashkeyTable
on cashkeyTable.keyword like '%' || xxxTable.PSDSC1 || '%'

Hmmm . . . that shoudl be probably really:

on ?.keywordlist like '%' || ?.keyword || '%'

It is a bit hard to tell from your naming which is which. You might also want to include delimiters.

For the exclusion, you then want where ?.keyword is null.

String concatenation functions/operators can differ among databases, so this might not be the exact syntax for your database.

As an aside, storing lists of values in a single column is a really bad idea. You should have a sepeate table with on row per keyword, rather than stuffing all the keywords into one column.

Upvotes: 2

Related Questions