Click here to Skip to main content
14,883,098 members
Please Sign up or sign in to vote.
4.50/5 (2 votes)
I want my search result to be in sorting order according to the word occurs in the the description. Let's say I have two description field:-

1). SHORT DESCRIPTION
2). LONG DESCRIPTION

I want to search an item '20 mm' in the two fields and the search result should yield the result in the following pattern:-

1). First the search should be on the exact word i.e., '20 mm'.

2). Secondly the search should be on '20 mm%' i.e., sentence starting from '20 mm' and ending in anything.

3). Thirdly the search should be on '%20 mm' i.e., sentence ending in '20 mm' and staring from anything.

4). Fourthly the search should be on '%20 mm%' i.e., sentence starting and ending in anything but there must be at least one occurrence of '20 mm' in it.

I have made a function in SQL 2005 that will insert each pattern into one table one by one and then will return me the whole table but the criteria i want is not getting fulfilled, I am actually getting somewhat mixed search.
If Some one knows how to take help of COMMON TYPE EXPRESSIONS to achieve this result then i will be very grateful to him/her OR some other funda to get the result.

Thanks

Varun Sareen
Software Developer
:)
Posted

Here is a simple idea in pseudo code...

Write a stored proc that will create a temporary table with the columns that you want returned. It will look something like
Create Table #TempResults
ID as int (make this an identity column)
ShortDescription varchar(20)
LongDescription varchar (200)


Run your 4 queries as an INSERT Select statement into your temporary table. Do all of your row ordering at insert time.

After you have queried your 4 different ways, then select your data.

SELECT *
FROM #TempResults
ORDER BY ID DESC


This will get the data from your temporary table in the order you put it in.
Make sure to drop the temporary table when you are done.


I have not dealt with COMMON TYPE EXPRESSIONS, so I won't try to give you code, but it looks like that may simplify the approach I offered. You can always do it the long way first and make sure it works, then refactor it to the cool code later.

Good luck and I hope this helps!

Hogan
   
You can just write like

Select * from (
Select '1' as lorder, * From Table where SHORTDESCRIPTION = '20min'
union all
Select '2' as lorder, * From Table where SHORTDESCRIPTION like '20min%'
union all
Select '3' as lorder, * From Table where SHORTDESCRIPTION like '%20min'
union all
Select '4' as lorder, * From Table where SHORTDESCRIPTION like '%20min%'
) as a
order by lorder
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900