SQL Compare Characters in two strings count total identical

Question

So the over all on this is I have two different systems and in both systems I have customers, unfortunately both systems allow you to type in the business name freehand so you end up with the example below.

Column A has a value of "St John Baptist Church"
Column B has a value of "John Baptist St Church"

What I need to come up with is a query that can compare the two columns to find the most closely matched values. From there I plan to write a web app where I can have someone go through and validate all of the entries. I would enter in some example of what I have done, but unfortunately I honestly dont even know if what I am asking for is even possible. I would think it is though in this day and age I am sure I am not the first one to try to attempt this.

Do some research on Levenshtein distance. There are implementations for SQL Server, and if your database is not too big, you might be able to use it. — Gordon Linoff, Sep 09 '14 at 14:09
[Levenshtein distance in T-SQL](http://stackoverflow.com/questions/560709/levenshtein-distance-in-t-sql) — Tim Schmelter, Sep 09 '14 at 14:09
Wholly freaking hell that is the quickest I have ever gotten a response on here — scripter78, Sep 09 '14 at 14:10
If one of you would so kindly enter your response as an answer I will mark this as answered. — scripter78, Sep 09 '14 at 14:58

score 1 · Accepted Answer · edited May 23 '17 at 12:24

You could try and create a script something like this php script to help you:

$words = array();
$duplicates = array();

function _compare($value, $key, $array) {
    global $duplicates;

    $diff = array_diff($array, $value);

    if (!empty($diff)) {
        $duplicates[$key] = array_keys($diff);
    }

    return $diff;
}

$mysqli = new mysqli('localhost', 'username', 'password', 'database');
$query = "SELECT id, business_name FROM table";

if ($result = $mysqli->query($query)) {
    while ($row = $result->fetch_object()) {
        $pattern = '#[^\w\s]+#i';
        $row->business_name = preg_replace($pattern, '', $row->business_name);
        $_words = explode(' ', $row->business_name);
        $diff = array_walk($words, '_compare', $_words);    
        $words[$row->id][] = $_words;

        $result->close();
    }
}

$mysqli->close();

This is not tested but you need something like this, because I don't think this is possible with SQL alone.

---------- EDIT ----------

Or you could do a research on what the guys in the comment recommend Levenshtein distance in T-SQL

Hope it helps, good luck!

Levenshtein distance in T-SQL was the answer – scripter78 Sep 09 '14 at 19:02 — scripter78, Sep 09 '14 at 19:02

SQL Compare Characters in two strings count total identical

1 Answers1