Advanced Comparison of two varchar fields

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.  

Thanks!
LVL 2
naisnetAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
In my opinion this is because you do
"A full text search is performed to find words that match in the two fields"
therefore full text does
"[...] search columns containing character-based data types for values that match the meaning and not just the exact wording of the words in the search condition. When FREETEXT is used, the full-text query engine internally performs the following actions on the freetext_string, assigns each term a weight, and then finds the matches.

Separates the string into individual words based on word boundaries (word-breaking).
Generates inflectional forms of the words (stemming).
Identifies a list of expansions or replacements for the terms based on matches in the thesaurus."


What I'm trying to say is that result sets may be different from start therefor may not be useful in a comparison for that matter.

If the data in the two fields to compare is NON-UNICODE I would use ASCII not CHAR and do the comparison after all strigs were converted to ASCII codes - you could follow the example from link below for doing that:

http://msdn.microsoft.com/en-us/library/ms177545.aspx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.