count number of occurances - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

count number of occurances

Author

Topic

cyberpd
Yak Posting Veteran

60 Posts

Posted - 2007-01-04 : 03:45:04

Let me first write down the table structure

Book_Details(CatCode,SubCatCode,CatName,SubCatName,BookName,AuthorName,
ISBNNo,BookPrice,No_Of_Pages,Book_Size,Description)

Suppose, there are 4 tuples. And each tuple has got the word "health" somewhere in the string in the following fields :- BookName, Description

Eg: BookName field contains "awekfhHEALTHjnkjfbgryhj"
Description field contains " this particular book is on HEALTH"

let the occurance of the word "HEALTH" is once in the first tuple.
let the occurance of the word "HEALTH" is twice in the secnd tuple.
let the occurance of the word "HEALTH" is thrice in the third tuple.
let the occurance of the word "HEALTH" is 4 times in the 4th tuple.

my question is :
write down a query that finds out all the tuples that contains the string "HEALTH" in the fields BookName, Description and show the result in a sorted order based on the maximum number of occurance of that string.

eg:- 4th tuple will be shown first
3rd tuple
2nd tuple
1st tuple

thanx and regards

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-04 : 06:33:37

[code]-- prepare sample data
declare @t table (data varchar(1000))

insert @t
select 'awekfhHEALTHjnkjfbgryhj' union all
select 'awekfhHEALTHjnkjHEALTHfbgryhj' union all
select 'aweHEALTHkfhHEALTHHEALTHHEALTHjnkjfHEALTHbgryhj' union all
select 'awekfhHEALTHjnkjfbHEALTHgrHEALTHyhj' union all
select 'awekfhHEALTHHEALTHjnkjfbgryhj' union all
select 'awekfhHEATHjnkjfbgryhj'

-- do the work
select data,
(len(data) - len(replace(data, 'health', ''))) / len('health') as occurencies
from @t
order by 2 desc,
1[/code]

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-01-04 : 08:01:13

or

order by len(data) - len(replace(data, 'health', '')) desc

Madhivanan

Failing to plan is Planning to fail

cyberpd
Yak Posting Veteran

60 Posts

Posted - 2007-01-04 : 08:15:38

this site is great...!!
a million thanx to peso.
and thnx to madhivanan..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-04 : 08:55:44

Madhi, why would you do another calculation again and waste more CPU cycles?
I think it is easier to just do a ORDER BY 2 DESC...

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-01-04 : 09:47:04

Well. It is becuase of this query

Select data from @t
order by len(data) - len(replace(data, 'health', '')) desc

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-04 : 10:04:36

Oh, you mean that only show the data and not present the actual number of tuplets?

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-01-04 : 10:28:12

Yes from the way questioner asked for

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources