建议一种方法:
1) UNPIVOT TableA, You will get:
ID1, COL1, CONTENT1
ID2, COL2, CONTENT2
ID3, COL3, CONTENT3
2) Join TableA and TableB by LIKE.
EXAMPLE:
-- Prepare Source Table
DECLARE @tblA Table (ID int identity, col1 varchar(100), col2 varchar(100),
col3 varchar(100))
INSERT INTO @tblA (col1, col2, col3)
SELECT 'this is a test', 'I am not sure', 'Give it a try.'
UNION ALL
SELECT 'who cares', 'No one knows', 'Why not'
UNION ALL
SELECT 'it is impossible', 'please let me know', 'be honest'
-- Prepare Keyword Table
DECLARE @tblB Table (KW varchar(10))
INSERT INTO @tblB(KW)
SELECT 'su'
UNION
SELECT 're'
UNION
SELECT 'am'
UNION
SELECT 'ok'
UNION
SELECT 'ou'
UNION
SELECT 'ur'
UNION
SELECT 'hones'
-- UnPivot Source TableA
DECLARE @tblResult Table(ID int, ColName varchar(20), Content varchar(100))
INSERT INTO @tblResult
select ID,
COL,
unPvt.Content
FROM @tblA
UNPIVOT (Content For Col in (Col1, Col2, Col3)) unPvt
-- Source Table
SELECT * FROM @tblA
-- Keywords Table
SELECT * FROM @tblB
-- Result Table
SELECT * FROM @tblResult a
INNER JOIN @tblB b on a.Content LIKE '%' + b.KW + '%'
ORDER BY a.ID, ColName