Thank you for your quick response. Table @t may store up to 25,000 rows (I used table variable just for demonstration purposes) and yes it is a varchar(max).
Yes, I would like "MySQL" to be included too.
Thank you.
select Word,sum((len(t.string)-len(replace(t.string,Word,'')))/len(Word)) as count
from @word as w
cross join @t as t
group by Word
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
Thank you!
I have two follow up questions.
Is there a way to change the query so that it only counts the word(s) once per row?
Also, I would like to count the rows that do not include any of those words and return the count in the result set (maybe with a union).
Thank you for your time.
Maybe something like this?
SELECT ISNULL( w.Word, 'None'),
COUNT(t.String),
ISNULL( SUM((LEN(t.String)-LEN(REPLACE(t.String,w.Word,'')))/LEN(w.Word)), COUNT(t.String))
FROM @word w
RIGHT
JOIN @t t ON String LIKE '%' + Word + '%'
GROUP BY Word
clayman (6/17/2014)
Thank you for your quick response. Table @t may store up to 25,000 rows (I used table variable just for demonstration purposes) and yes it is a varchar(max).Yes, I would like "MySQL" to be included too.
Thank you.
Understood that it IS a VARCHAR(MAX). I believe the question is, does it have to be? The reason why everyone is asking that is because there's a function that might be able to do a whole lot more than you might expect for such a thing. The only trouble with it is that it runs twice as slow if it's converted to VARCHAR(MAX).
--Jeff Moden
Change is inevitable... Change for the better is not.
Jeff Moden (6/17/2014)
clayman (6/17/2014)
Thank you for your quick response. Table @t may store up to 25,000 rows (I used table variable just for demonstration purposes) and yes it is a varchar(max).Yes, I would like "MySQL" to be included too.
Thank you.
Understood that it IS a VARCHAR(MAX). I believe the question is, does it have to be? The reason why everyone is asking that is because there's a function that might be able to do a whole lot more than you might expect for such a thing. The only trouble with it is that it runs twice as slow if it's converted to VARCHAR(MAX).
The data type in the database is TEXT (I know..) so I'm converting it to VARCHAR(MAX) in the stored procedure. Whether it has to be VARCHAR(MAX) or not, I'm not sure about that. All I know these string can pretty long and I don't want to lose any data.
clayman (6/17/2014)
Thank you!I have two follow up questions.
Is there a way to change the query so that it only counts the word(s) once per row?
Also, I would like to count the rows that do not include any of those words and return the count in the result set (maybe with a union).
Thank you for your time.
Yes, try this
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
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
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 WordSynUNION 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
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply