Search for last name by any spelling

[AJBJ]

I am tring to do a search in MSSQL on a column in the database.  If the user types in the lastname field smiths or smeth, the records return would be anything near that spelling.  I am looking for all the 'smith' last name in the column and I have tried every which way with the percent.  And I am not returning any records.  Here is my current code.  and lastname like '%#form.lname#'

thanks

[tclaremont]

By putting the % at the beginning of the string, you are only asking for results where there is something BEFORE the letters SMITH. So, for example ASMITH and BSMITH and ABSMITH would all be returned.

Taking it a step further, you could do %SMITH% if you wanted to find something like ASMITHSON, or BSMITHY.

If you are looking for variations is spelling, you will probably have to search for a custom tag or some such thing.

[ilssac]

If you want to use FUZZY spelling, where the database tryies to match alternate ways to spell names.  You will probably want to look at SOUNDEX functionality.  This is a feature of many entrprise database, I beleive MSSQL has it.  It will allow you to search for a name like "SMITH" and the database will returns names like "SMITH" OR "SMYTH"

[Guest]

This may be too much work, but you could use the soundex library at CFLIb to go through your DB, converting the names and putting them in a new column of the table (soundex_lastname).  Then you could soundex() the user input in the query and compare it to the soundex_lastname field in the database - that way your DB doesn't have to support soundex.

Note, that soundex only really works well with anglicised names, it doesn't work very well for international names or any other text as far as I can see.  Check out the link for more information.