Group: General Forum Members
Last Login: Tuesday, July 29, 2014 3:55 PM
I would also like to search for synonym words, however they should not be counted separately. Note, these synonyms could also be placed in a new table, they don't have to be comma separated strings.
Would full text indexing make more sense in this scenario (then I could just add those synonyms in the tsenu.xml file)? Thank you.
DECLARE @t TABLE (Id INT IDENTITY(1,1), String VARCHAR(MAX))
INSERT INTO @t
SELECT 'I bought a new shirt, I purchased a new t-shirt.' AS String UNION ALL
SELECT 'He looks very miserable today. He looks very sad today.' UNION ALL
SELECT 'I’ve been searching for that book for weeks!I’ve been looking for that book for weeks!' UNION ALL
SELECT 'He looks sad since he could not afford to buy a shirt'
DECLARE @word TABLE (Id INT IDENTITY(1,1), Word VARCHAR(100), WordSyn VARCHAR(500))
INSERT INTO @word
SELECT 'buy' AS Word, 'bought, purchase, purchased, obtain, obtained' AS WordSyn UNION ALL
SELECT 'sad', 'miserable, unhappy, depressed, gloomy' UNION ALL
SELECT 'search', 'look for, looking for, looked for, searching for, search for, searched for'
select isnull(Word,'No Match') as Word,sum(1) as count
from @t t
left outer join @word as w
on t.string like '%'+w.word+'%'
group by Word
-- Expected output would be
select 'buy' as [Word], 3 as [count] UNION ALL
select 'sad', 3 UNION ALL
select 'search', 2