where clause in order - 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 2008 Forums

Transact-SQL (2008)

where clause in order

Author

Topic

neil_akoga
Yak Posting Veteran

56 Posts

Posted - 2011-04-20 : 05:13:06

is it possible to have a where clause in the order? for example i am creating a dynamic query as follows for a search term

"small blue cup"

I am then splitting up my search clause and searching as follows

select * from tblWhatever where column1 like '%small%' or column1 like '%blue%' or column1 like '%cup%'

is it possible to do something like the following

select * from tblWhatever where column1 like '%small%' or column1 like '%blue%' or column1 like '%cup%' order by (column1 like '%small blue cup%'), column1 like '%small%', column1 like '%blue%' etc

i can't use full text indexing or anything fancy so i need a quick and dirty way of ranking against the full term and then the sub search terms if possible

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-04-20 : 05:26:43

try this:
ORDER BY
CASE
WHEN column1 like '%small blue cup%' THEN 1
WHEN column1 like '%small%' THEN 2
WHEN column1 like '%blue%' THEN 3
ELSE 4
END

No, you're never too old to Yak'n'Roll if you're too young to die.

neil_akoga
Yak Posting Veteran

56 Posts

Posted - 2011-04-20 : 06:05:11

perfect webfred. cheers for this

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources