Counting multiple key words in a single query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Counting multiple key words in a single query

Author

Topic

WebKill
Starting Member

32 Posts

Posted - 2014-01-08 : 18:38:35

I have data in which I need to find key words within a string of text, for instance given the table:

ID, Input
01, squares and triangles
02, triangles and circles
03, squares and octagons

I would like to be able to report the number of times a key word appears:

squares 2
triangles 2
circles 1
octagons 1

Right now I just have multiple queries each looking for a single key word or phrase then using union to put them together, but I was wondering if there is a better way?

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2014-01-08 : 23:19:19

Put them in a table then do something like this:

select words.word, count(*) from words inner join textTable on textTable.theText like '%'+words.word+'%'
group by words.word

only works one per line of text though.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-09 : 05:08:28

are words always separated by and?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

WebKill
Starting Member

32 Posts

Posted - 2014-01-09 : 11:01:18

No, this was just a simple example, the real data is longer stings of text. Each piece of information is comma delimited but has more info as well, so a better data example might be:

ID, Input
01, squares (these have 4 sides), triangles (these have 3 sides)
02, triangles (these have 3 sides), and circles (these have no sides)
03, squares (these have 4 sides, and octagons (these have 8 sides)

Right now I have:
select 'Squares', count(*) from Table where Input like '%squares%'
UNION
select 'Triangles', count(*) from Table where Input like '%triangles%'
and so on...

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-01-09 : 17:48:05

Full-text indexing is the best solution for this.

If you can't do that, then create a table of the search values and do a join to them -- that way at least SQL may be able to do multiple searches in a single pass of the data.

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2014-01-09 : 19:18:38

My solution works for you I think. You just need to create a table with the words you are interested in ("Words" in my example).
Slightly corrected version:

create table #textTable ( id int, input varchar(100) )
insert into #textTable values(01, 'squares (these have 4 sides), triangles (these have 3 sides)')
insert into #textTable values(02, 'triangles (these have 3 sides), and circles (these have no sides)')
insert into #textTable values(03, 'squares (these have 4 sides, and octagons (these have 8 sides)')

create table #words(word varchar(20))
insert into #words values('squares')
insert into #words values('triangles ')
insert into #words values('circles')
insert into #words values('octagons')

select #words.word, count(*) from #words inner join #textTable on #textTable.input like '%'+#words.word+'%'
group by #words.word

Output:

circles 1
octagons 1
squares 2
triangles 2

WebKill
Starting Member

32 Posts

Posted - 2014-01-10 : 10:19:15

That's a good solution, however it may not be optimal for me. Is there a way to use this methodology with an array?

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2014-01-10 : 17:53:12

Yes, you can insert your array into a temporary table.
If you are using .NET check out SqlBulkCopy

Of course this leads to the question "where does your array of words originate"? If it's already in the database then you're halfway there.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-10 : 23:18:10

quote:
Originally posted by WebKill

That's a good solution, however it may not be optimal for me. Is there a way to use this methodology with an array?

see how arrays are implemented in sql
http://www.sommarskog.se/arrays-in-sql.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources