Search code examples
sqlsql-server-2008t-sqlselectsql-order-by

SQL Query to find relevant result

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

Solution

  • Try this: 

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