Matching Char in Form to dB

[johk02]

What I want to achieve is when someone enters the name of their suburb (say Abbotsford) but they spell it wrong (say Abotsford) how do I match the characters entered to the ones in the dB??
This is for a registration form and they are also entering the Postcode which means the numbers of matches are limited to at the most 5 to 6 suburbs.

Thanks
Jonas

 

[r937]

soundex comes to mind

r937.com | rudy.ca

 

[johk02]

I was thinking along the lines of using a regex to match or see if the characters that are entered in to the form fields match the ones in the query.
My idea was to first retrieve the suburbs/localities that match a particular postcode and then match the characters that are entered to one of the retrieved suburbs.
The reason of doing it this way is that I can't rely on that the suburb entered is spelt correctly.

Thanks

Jonas

 

[FALCONSEYE]

you can also try

<cfquery .... >
SELECT stuff
FROM myTable
WHERE suburb LIKE '%mySuburb%'
</cfquery>

one thing to note though, to get Abbotsford when somebody types Abotsford, you may want to modify the query as
LIKE 'Ab%'

hope it helps...

http://www.jetansolutions.com

 

[philhege]

Try using a combination of SOUNDEX and DIFFERENCE (MS SQL assumed here). I use this function to match records in my DB with postal service data:

difference(rtrim(ltrim(zp_Streetname)),rtrim(ltrim(ret_streetname))) >=3

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.