Take the 2-minute tour ×
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. It's 100% free, no registration required.

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?

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
4  
It does matter. Each RDBMS has different ways of achieving the same thing. –  Phil Jan 15 '13 at 15:00

1 Answer 1

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

SELECT Y.*
FROM   YourTable Y,
(SELECT 4 AS NumChar) T /* Change this line to alter desired length*/
WHERE  EXISTS (SELECT *
               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

 
discard

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.