|
|
Posted by Ed Murphy on 10/18/06 15:54
Bill wrote:
> Lionel Pinkhard wrote:
>> I currently have a select query with "Description = 'input from user
>> here'" which basically returns the associated row containing the
>> Description field exactly as typed by the user, however, would it be
>> possible to, if not found, return the closest match? I am using
>> Microsoft Visual Studio 2005 with C# as language and Microsoft SQL
>> Server 2005 as database.
> I am making an assumption that you can return multiple rows to the user
>
> to pick from. And that you want the exact match to appear as the most
> relevant and first in the list.
>
>
> SELECT 1 AS sort_order, row_data
> FROM tbl
> WHERE Description = 'input'
> UNION
> SELECT 2 AS sort_order, row_data
> FROM tbl
> WHERE Description like '%input%'
> AND Description != 'input'
> ORDER BY sort_order, ?
[top-posting fixed]
Does this work? I forget whether ORDER BY accepts calculated columns.
select
case when Description = 'input' then 1
when Description like '%input%' then 2
else -1 -- can't happen
end as sort_order,
row_data
from tbl
where Description like '%input%'
order by sort_order -- and optionally one or more additional columns
Navigation:
[Reply to this message]
|