Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 - if I set similarity to 4 - means match 4 character in row

script should find alexander and cause they have 4 charachters match (alex)

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

share|improve this question
What RDBMS do you use? And I wouldn't call a name column id. – dezso Jan 15 '13 at 14:29
I guess it does not matter, lets say MySQL and id was just sample – oleg Jan 15 '13 at 14:56
It does matter. Each RDBMS has different ways of achieving the same thing. – Phil Jan 15 '13 at 15:00

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

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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