Advanced Comparison of two varchar fields
Posted on 2011-10-06
I have two fields that I need to compare and DIFFERENCE isn't quite doing the job. Here's what I have so far and what I'd like to do with it:
1. A full text search is performed to find words that match in the two fields
2. Both the found and reference fields are converted to lower case and stripped of all non-character data
3. The fields are then compared using DIFFERENCE
DIFFERENCE isn't quite doing the job, so if anyone has any suggestions for a replacement? I was thinking about adding the CHAR value of each character in the string of both fields and ranking the results based on similarity in overall number (e.g. If the reference field's overall value was 54 and the full text search match was 52, this result would appear near the top. If the reference's value was 54 and the full text match was 187, that would appear towards the bottom.).
I'm not sure, however, how I would do this as I'm not vastly experienced in string functions. I do realize that I could do a more exact match with the full text search, however, for this application I need all available matches...just using better ranking.