Order By Number Of Instances Found in Field

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)

Order By Number Of Instances Found in Field

Author

Topic

jimmy.hunt
Starting Member

21 Posts

Posted - 2006-03-22 : 23:22:42

Can you order a query result by the number of instances found in a field? For example if I write a query like this:

select * from table where column = '%test string%';

and some records had more instances of 'test string' in their varchar than others can we order it by instances? If this query found 3 records, 1 had 3 instances, 1 had 2, and 1 had 1 can I put the 1 with the 3 instances on top, 2 in the middle, and 1 on the bottom.

Thanks!
James

James Hunt

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-22 : 23:31:22

Is this what you want ?

declare	@table table
	(
		col	varchar(100)
	)

declare	@search	varchar(100)

select	@search	= 'test string'
insert into @table
select	'This is a test string'				union all
select	'This is not a testing string'			union all
select	'This is also a test string'			union all
select 	'test test string test string test string'	union all
select	'test string test string test string'		union all
select	'Another test string and test string'

select	col, (len(col) - len(replace(col, @search, ''))) / len(@search) as cnt
from	@table
where	col	like '%' + @search + '%'
order by cnt desc

KH
Choice is an illusion, created between those with power, and those without.
Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant

jimmy.hunt
Starting Member

21 Posts

Posted - 2006-03-22 : 23:50:51

Can you please explain the last part of this script?

Thanks!
James

quote:
Originally posted by khtan

Is this what you want ?
declare @table table
(
col varchar(100)
)

declare @search varchar(100)

select @search = 'test string'
insert into @table
select 'This is a test string' union all
select 'This is not a testing string' union all
select 'This is also a test string' union all
select 'test test string test string test string' union all
select 'test string test string test string' union all
select 'Another test string and test string'

select col, (len(col) - len(replace(col, @search, ''))) / len(@search) as cnt
from @table
where col like '%' + @search + '%'
order by cnt desc

KH
Choice is an illusion, created between those with power, and those without.
Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant

James Hunt

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-23 : 00:04:28

Sure.

quote:
select col, (len(col) - len(replace(col, @search, ''))) / len(@search) as cnt
from @table
where col like '%' + @search + '%'
order by cnt desc

To find the number of occurance of the search string in the column, the trick is remove the search string in the column, find the difference original col length and the length after removal of search string and divide by the length of the search string.

Try this. You should be clearer with this

select	col, (len(col) - len(replace(col, @search, ''))) / len(@search) as cnt,
	len(col) as [Data Length],
	replace(col, @search, '') as [After Removal],
	len(replace(col, @search, '')) as [Data Length - @Search],
	len(@search) as [Length of @Search]
from	@table
where	col	like '%' + @search + '%'
order by cnt desc

jimmy.hunt
Starting Member

21 Posts

Posted - 2006-03-23 : 00:08:28

Wow! That is really an amazing query.

Thanks!!!!!
James Hunt

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources