Optimized enough - 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

SQL Server Development (2000)

Optimized enough

Author

Topic

paomeow
Starting Member

5 Posts

Posted - 2004-11-22 : 23:41:02

Hi can anyone give me advice please? Is this sql optimized enough? All I wanted was to arrange search result in categories as indicated in the sql. I'm worried that this is an overkill solution and I can't think of any other good solution.

select * from @table where @name like '@value'
union all
select * from @table where @name like '@value%'
union all
select * from @table where @name like '%_@value_%'
union all
select * from @table where @name like '%@value'

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-11-22 : 23:48:39

you could use a case statement:


Select 
	*, --list your columns
	orderId = case 
			when name like @value then 1
			when name like @value + '%' then 2
			when name like '%_' + @value + '_%' then 3
			when name like '%' + @value then 4
				else 5 end
From @table
where name like '%' + @value + '%'
Order By orderId

Corey

Kristen
Test

22859 Posts

Posted - 2004-11-23 : 00:22:34

Is @value a "word"? FWIW We give a higher ranking when there is whitespace around the search term. Perhaps something like:

when name = @value then 1
when name like @value + ' %' then 2
when name like '% ' + @value + ' %' then 3
when name like @value + '%' then 4
when name like '% ' + @value + '%' then 5 -- Word starts-with
when name like '%_' + @value + '_%' then 6
when name like '%' + @value then 7

Kristen

paomeow
Starting Member

5 Posts

Posted - 2005-01-11 : 22:22:44

thank you very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources