|
|
Posted by Bill on 10/18/06 13:49
Lionel,
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, ?
You can use multiple filters and unions to modify and rank the results
ie. WHERE DESCRIPTION like 'input%'
description like substring(input, 1, 10) + '%' --Not sure if this
works outside of a dynamically build query.
You can also use the CHARINDEX funtion to look for some or all of the
input
WHERE CHARINDEX(substring('input', 1, 100), description, 1) > 0
I put the substring in above to suggest that you can match on only part
of the input if you want.
Lionel Pinkhard wrote:
> Hi
>
> 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.
>
> Regards,
>
> Lionel Pinkhard
>
>
>
> ---
> avast! Antivirus: Outbound message clean.
> Virus Database (VPS): 0642-0, 2006/10/17
> Tested on: 2006/10/18 12:46:07 PM
> avast! - copyright (c) 1988-2006 ALWIL Software.
> http://www.avast.com
Navigation:
[Reply to this message]
|