Best match for string in database

[kcashel]

I need to select the best match for a name in a database as a default value. Something like the SQL select code "WHERE name LIKE 'smith'", only if it fails, it needs to get the next best match, "smithe" or "smithers" or whatever. Using a SQL statement (or a data view) won't do it though, because I need to display the entire contents of the database I'm matching to. (The user is trying to find out if the name's already in there, misspelled or otherwise manged, and select it if it exists, so the program can drop in the appropriate primary key.)

All help appreciated.

[hellswraith]

I can't remember the wildcard character for SQL server, but it might be * or ?.

Just put "Where Like 'smith*'"

With the results, look for the exact match....'smith'

if not in there, sort the results and start at the first one.

[TLawson]

the wildcard for SQL is a % which means 1 or more charicters in this place, theres another for 1 charicter in this place... i think its an * but i'm not sure you might want something like if smith failed then dim the first charicter as a variable and do a SQL query based on the varible

[hellswraith]

If you do the search for anything like the name your after, you only have to access the DB once. Even if the actual value 'smith' isn't in there, you will have the others that kind of match it already. This is in the place of doing a search for smith, not finding it, then doing another search for anything like it.