SQL Query to find relevant result

Question

I have a sql table with one varchar column. Some of the data it contains is like:

sadkjlsakjd
Physics Test 2
Test
Test 1
P Test C
Physics Test None
dstestsad

Now, I need a query that gives the most relevant record first when I search with 'Test' keyword. I am expecting:

Test
Test 1
<Then other records where Test comes in between>

I have some how achieved this query with Temp table and intersection but not at all happy with what I written. I have a feeling that there should be something easy and fast.

Please suggest.

Thanks

Is that line with data representing several different records or just one record? — Mariano D'Ascanio, Jul 10 '14 at 04:36
What defines the relevance? The string you are searching starts with the keyword? — Jorge Campos, Jul 10 '14 at 04:40
[This](http://stackoverflow.com/questions/8636911/how-to-find-strings-which-are-similar-to-given-string-in-sql-server) addresses a similar issue, I think. — Mariano D'Ascanio, Jul 10 '14 at 04:42
Did you try to use CONTAINS and FREETEXT and maybe CONTAINSTABLE and FREETEXTTABLE to generate a ranking value to decide on how many fields to return. — etr, Jul 10 '14 at 04:52

score 0 · Answer 1 · answered Jul 10 '14 at 04:41

0

May be something like this

SELECT * FROM Your_Table WHERE Your_Column LIKE '%Test%'
ORDER BY CASE WHEN Your_Column LIKE 'Test%' THEN 0 ELSE 1 END,Your_Column

answered Jul 10 '14 at 04:41

Vignesh Kumar A

27,863
13
63
115

score 0 · Accepted Answer · answered Jul 10 '14 at 04:42

0

Try this:

SELECT colName 
FROM tableName 
WHERE colName LIKE '%Test%'
ORDER BY CASE WHEN colName LIKE 'Test%' THEN 1 ELSE 2 END, colName

answered Jul 10 '14 at 04:42

Saharsh Shah

28,687
8
48
83

Thanks for the answer. But I did not understand this. What I have been told till now is Order by 1 means sort by 1st column and order by 2 means sort by 2nd column – user2861226 Jul 10 '14 at 15:07

SQL Query to find relevant result

2 Answers2