Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

String occurrence count with Cross apply Expand / Collapse
Author
Message
Posted Monday, July 14, 2014 6:54 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 25, 2014 10:23 PM
Points: 277, Visits: 1,691
Thank you.

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

Post #1592388
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse