For example I have a column id and column email. I would like to find all values from id and column that share similar pattern.

let's say: in id 'column' I have Alexander and in 'email' column - walex@sample.com. if I set similarity to 4 - means match 4 character in row

script should find alexander and walex@sample.com cause they have 4 charachters match (alex)

What's the best way to accomplish something like this?


If you have a numbers table populated with integers up to the max length the strings in the data can be

FROM   YourTable Y,
(SELECT 4 AS NumChar) T /* Change this line to alter desired length*/
               FROM   Numbers N
               WHERE  N.N < LEAST(LENGTH(name), LENGTH(email)) - FLOOR(NumChar/2)
                      AND GREATEST(LOCATE(SUBSTRING(Name, N, NumChar), email), 
                                   LOCATE(SUBSTRING(email, N, NumChar), Name)) > 0)

SQL Fiddle

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.