MySql Php Find Similar Values

Question

I run a website where users have a username. They can change their usernames whenever they want. When them change their name, we check that that name isn't currently being used and then allow or not allow the change. On our site people often like to change their username to copy other peoples (make their name very similar to confuse other people of their identity). This isn't uncommon for the type of site we run.

Is way to easily check for usernames that are somewhat similar using a simple query?

Here are some examples of usernames that we would like to have a query match up.

testingman1 = testingman11
lionhead = Iionhead (one has an l and the other has a capital i)
sleepybears = sleeepybears

Any way to do a character by character count of the same letters in the same position and then determine based on the percentage if it is a copy of another user?

I know I'll most likely have to write a custom function, but just looking for some advice on how to make it as painless and not very system taxing process.

Thanks a ton! I didn't know such a thing existed! I'll pry implement the php version of it (php.net/manual/en/function.levenshtein.php). Not a big fan of creating functions in MySql. — user2027231
– user2027231, Commented Jul 31, 2013 at 16:24
just be careful. you may end up with false positives, e.g. two legitmately different names that do only differ by one character, like tommi and tammi. — Marc B
– Marc B, Commented Jul 31, 2013 at 16:27
Have a look at the soundex function: php.net/manual/en/function.soundex.php. You would use $u = soundex( $username ); $u2 = soundex( $newName ); if( $u1 == $u2 ) { then pick another name } — user2417483
– user2417483, Commented Jul 31, 2013 at 16:34

Daniel Dudas · Accepted Answer · 2013-07-31 16:52:41Z

1

You can use

levenshtein(str1, str2) that will return an integer witch is the distance between the two strings. In PHP if one string is longer that 255 characters the function will return -1.
More info: http://php.net/manual/en/function.levenshtein.php
or if you want in percent you can use similar_text ( string $first , string $second, [, float &$percent ] ) witch pass in the 3rd parameter the percent of similarity More info: http://www.php.net/manual/en/function.similar-text.php

answered Jul 31, 2013 at 16:52

Daniel Dudas

3,0123 gold badges29 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

symcbean Over a year ago

Loading all the data into PHP to compare it? Not very efficient. There are several implementations of levenstein for MySQL, e.g. github.com/MartinZottmann/mysql-levenshtein I expect there are other functions for string comparison too.

Collectives™ on Stack Overflow

MySql Php Find Similar Values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related