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