March 24, 2011 at 9:06 am
I am currently working on a project for a state agency that has requested "auto complete" functionality instead of drop down boxes for their search features. What our client wants is a search of the entire field with results returned for any match in the string. For example, if they search for "ring" it would return records of "ring" and "bring" if those values were in the field to be searched.
The first developer who took a stab at this put his code inline and used the syntax
SELECT {field}
FROM {table}
WHERE {field} LIKE '%value%'
Of course, I wasn't happy with this solution and have moved this into a stored procedure and added a parameter to the search
SET @searchParameter = '%' + COALESCE(@searchParameter , '') + '%'
SELECT {field}
FROM {table}
WHERE {field} LIKE @searchParameter
Even with this, I'm not entirely happy with the solution. I am worried about performance as more data is added to the system, but I'm not sure there are any better options. Any opinions on how to improve on this?
March 24, 2011 at 11:54 am
Full text indexes may be a better approach than trying to do leading wild card searches on a very large table.
The probability of survival is inversely proportional to the angle of arrival.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply