MySQL Query based on string

Question

This query has been bothering me for the past 10 hours. Here we go:

I want to do a comparison to some data I am pulling. I am pulling names and I want to remove names that are similar and have them not return in the query.

Example:

I have the following names:

Seaside Heights
Seaside HGTS
Talladega
Tornkal Center
Tornkal CTR
Yonkers
Zebraville

I want it to return like this:

Seaside Heights
Talladega
Tornkal Center
Yonkers
Zebraville

Basically I think it should be substring(name, 0, 8) to get the first 8 characters then run that 8 characters against the next entry and if they match to ignore it.

Maybe I am thinking way to deep into this. Any insight or concepts that might work will be appreciated.

what is the relation of output to input?? what you will have for substring(name, 0, 8) ?? — Fahim Parkar
– Fahim Parkar, Commented Mar 3, 2012 at 4:59
You only want to ignore an entry if its first 8 characters match the first 8 characters of another entry? What if the first 7 characters match? — CFL_Jeff
– CFL_Jeff, Commented Mar 3, 2012 at 5:04
I don't think this question deserves any upvotes as it's way too ambiguous and obscure — Your Common Sense
– Your Common Sense, Commented Mar 3, 2012 at 5:19

William · Accepted Answer · 2012-03-03 04:59:38Z

1

First, you would query all the data.

Then for every record returned you want to run the LCS algorithm (Longest Common Subsequence).

If the longest common Subsequence between two different records is of a number of your choosing then you can class them as similar.

http://en.wikipedia.org/wiki/Longest_common_subsequence_problem

edit: It just so happens there's a nice PHP function for this: http://php.net/manual/en/function.similar-text.php

answered Mar 3, 2012 at 4:59

William

1864 silver badges16 bronze badges

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

Comments

score 1 · Accepted Answer · 2012-03-03 05:12:22Z

1

Try below :

If the difference between strings is similar as you explained in example.

 select names from tablename group by substring_index(names," ",1)

edited Mar 3, 2012 at 5:12

answered Mar 3, 2012 at 5:07

user319198

2 Comments

CFL_Jeff Over a year ago

Group by with no aggregate functions?

user319198 Over a year ago

MySQL, however, allows this when simply chooses one value.

user610217 · Accepted Answer · 2012-03-03 05:02:55Z

0

You might want to take a look at soundex. It won't be perfect, but it could get you in the ball park.

answered Mar 3, 2012 at 5:02

user610217

Comments

Amber · Accepted Answer · 2012-03-03 05:05:23Z

0

If the differences between strings are limited to a small set of abbreviations (HGTS <-> Heights, CTR <-> Center, etc), you might just want to keep a table of those and replace the abbreviations with the full versions, then check for uniqueness.

answered Mar 3, 2012 at 5:05

Amber

531k89 gold badges643 silver badges558 bronze badges

Collectives™ on Stack Overflow

MySQL Query based on string

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related