How to get Search Result in Sorting Order of it's occurrence in a string in database ?

Question

4.50/5 (2 votes)

See more:

.NET2.0

SQL-Server-2005

, +

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 26-Mar-10 1:50am

Varun Sareen

Add a Solution

2 solutions

Solution 2

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

Posted 27-Mar-10 0:54am

Goutam Patra

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

snorkie · Accepted Answer · 2010-03-26T07:32:00

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