Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-09-14 : 09:50:04
|
| ABHIJEET writes "My problem is String Comparision Using SOUNDEX & DIFFERENCE.The SOUNDEX & DIFFERENCE function are doing good but there is limitation with them that SOUNDEX does not not compare after any special charater in between the String.Ex. SELECT SOUNDEX('St. Jhon') AS NAME1, SOUNDEX('St. Thomas') AS NAME2In output : both values are same. SELECT DIFFERENCE('St. Jhon','St. Thomas') AS DIFFOUTPUT IS DIFF ------ 4 -- Both are equal.(Best match) I want filter the data on the basis of conversation. Ex'Abhijeet' & 'Abhijit' both have different spelling but Pronounsation(conversation) is same. SOUNDEX is working fine with such type of cases. But what about strings(varchar datatype) which have special character(including space' ') inbetween them ???Plz Look into the matter...I am using SQL SERVER version SQL 8.00.194,Windows XP -Professional AS Operating System With Service pack-2 installed in it." |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2006-09-14 : 09:53:26
|
| I looked into the matter (for about 0.0001 seconds) and thought, "why not simply remove the non-alphabetic characters from the string before I do SOUNDEX or DIFFERENCE on them?"SELECT SOUNDEX(Replace(Replace('St. John', '.', ' '), ' ', ''))SELECT SOUNDEX(Replace(Replace('St. Thomas', '.', ' '), ' ', ''))SELECT DIFFERENCE(Replace(Replace('St. John', '.', ' '), ' ', ''), Replace(Replace('St. Thomas', '.', ' '), ' ', '')) |
 |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-09-14 : 10:39:01
|
| Why are you using SOUNDEX anyway? It is woefully poor for fuzzy-search string comparisons."I have HAD it with these muthu-f$#%in' cursors in my muthu-f$#%in' database!" |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-09-14 : 11:20:48
|
Guess you missed this in SQL Server Books Online:"The SOUNDEX function converts a character string to a four-digit code for use in a comparison. Vowels are ignored in the comparison. Nonalphabetic characters are used to terminate the comparison."So with the following code, you are comparing 'St' to 'St'SELECT DIFFERENCE('St Jhon','St Thomas') AS DIFFCODO ERGO SUM |
|
|
|
|
|
|
|
|
String Comparision