SQL Search function to order by appearance in columns

Question

I have an SQL search function built up as following:

SELECT
    *
FROM
    Table
WHERE
    Question LIKE @SearchString
    OR Answer LIKE @SearchString
    OR Keywords LIKE @SearchString

And I want to order them by relevance, meaning the results where Keywords LIKE @SearchString on top, followed by Answer LIKE @SearchString and then Question LIKE @SearchString. But I can't seem to figure out how despite several googling efforts.

I found somewhere that

ORDER BY 
    CASE 
        WHEN CHARINDEX(FAQ_FAQ.Keywords, @SearchString, 1) > 0 THEN 0
    ELSE 1
END ASC,

Might work, but apparently it doesn't.

Thx in advance

score 3 · Accepted Answer · answered Jan 10 '12 at 21:26

3

SELECT
    *
FROM
    Table
WHERE
    Keywords LIKE @SearchString
    OR Answer LIKE @SearchString
    OR Question LIKE @SearchString
ORDER BY
    CASE WHEN Keywords LIKE @SearchString THEN 0
         WHEN Answer LIKE @SearchString THEN 1
         ELSE 2
    END

answered Jan 10 '12 at 21:26

ypercubeᵀᴹ

113,259
19
174
235

this did the trick! thx! tried something similar before, but the "LIKE" gave me errors for some reason – Mats Raemen Jan 10 '12 at 21:44

score 1 · Answer 2 · answered Jan 10 '12 at 21:25

1

You could always use the UNION ALL route:

SELECT 
    * 
FROM 
    Table 
WHERE 
    Question LIKE @SearchString 

union all

SELECT 
    * 
FROM 
    Table 
WHERE 
    Answer LIKE @SearchString 

union all

SELECT 
    * 
FROM 
    Table 
WHERE 
    Keywords LIKE @SearchString

You could even take it as far as this:

select *
from
(
    SELECT 
        *, 1 as OrderRank
    FROM 
        Table 
    WHERE 
        Question LIKE @SearchString 

    union all

    SELECT 
        *, 2 as OrderRank 
    FROM 
        Table 
    WHERE 
        Answer LIKE @SearchString 

    union all

    SELECT 
        *, 3 as OrderRank 
    FROM 
        Table 
    WHERE 
        Keywords LIKE @SearchString 
)a
order by OrderRank

answered Jan 10 '12 at 21:25

for a union trick to work, you should add some pseudo-column like 1,2,3 and then wrap the entire thing into another select so you can order by the new numbers. – Randy Jan 10 '12 at 21:28
@Randy that's exactly what I did :) – Jan 10 '12 at 21:28
The 2nd query is pretty good if you don't mind (or want!) duplicate (or triplicate) rows of the table to be shown when these rows match 2 or 3 of the criteria. – ypercubeᵀᴹ Jan 10 '12 at 21:43

score 0 · Answer 3 · answered Jan 10 '12 at 21:26

0

You can select the three things distinctly and union them together so get the results you are looking for. There may be a better way but I am not aware of it. Are you familiar with how to union?

answered Jan 10 '12 at 21:26

Jordan

2,708
4
22
35

René Nyffenegger · Answer 4 · 2012-01-10T21:32:52.947

0

Somthing like this maybe?

select * from (
    select
      t.*,
      case when Question LIKE @SearchString then 1 
           when Answer   LIKE @SearchString then 2
           when Keywords LIKE @SearchString then 3
      end crit
     from table t
) where crit > 0
order by crit

edited Jan 10 '12 at 21:32

answered Jan 10 '12 at 21:27

René Nyffenegger

39,402
33
158
293

SQL Search function to order by appearance in columns

4 Answers4