Using LIKE in SQL Server to identify strings

Question

I am writing a program that performs operations on a database of Football matches and data. One of the issues that I have is that my source data does not have consistent naming of each Team. So Leyton Orient could appear as L Orient. Most of the time this team is listed as L Orient. So I need to find the closest match to a team name when it does not appear in the database team name list exactly as it appears in the data that I am importing. Currently in my database I have a table 'Team' with a data sample as follows:

TeamID  TeamName    TeamLocation
1       Arsenal     England
2       Aston Villa England
3       L Orient    England

If the name 'Leyton Orient' appears in the data being imported I need to match this to L Orient and get the TeamID 3. My question is, can I use the LIKE function to achieve this in a case where the team name is longer than the name in the database? I have figured out that if I had 'Leyton Orient' in the table and was importing 'L Orient' I could locate the correct entry with:

SELECT TeamName FROM Team WHERE TeamName LIKE '%l%orient%';

But can I do it the other way around? Also, I could have an example like Manchester United and I want to import Man Utd. I could find this by putting a % sign between every character like this:

SELECT TeamName FROM Team WHERE TeamName LIKE '%M%a%n%U%t%d%';

But is there a better way?

Finally, and this might be better put in another question, I would like not to have to search for the correct team when the way a team is named is repeated, i.e. I would like to store alternative spellings/aliases for teams in order to find the correct team entry quickly. Can anybody advise on how I might approach this? Thanks

This is the sort of problem I'd usually solve with a mark one eyeball - find all distinct team names that haven't yet been resolved, have a human decide which team is actually meant - update the mappings and continue. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Apr 11, 2014 at 13:09
@Damien_The_Unbeliever This seems like a good solution. Also Wherever the information is coming from consider adding validation before it hits the table. — DidIReallyWriteThat
– DidIReallyWriteThat, Commented Apr 11, 2014 at 13:12
@CalvinSmith's suggestion could work, upon inputting the team name, you could ask the user to select one of the pre-defined options. These options would have to be seeded from a list or a text search though. — BanksySan
– BanksySan, Commented Apr 12, 2014 at 0:36
Yeah, thanks. I'm going to look into some of the options here. Currently favouring an Alias list with options to select an existing team or add a new one. — Steve W
– Steve W, Commented Apr 12, 2014 at 13:32

BanksySan · Accepted Answer · 2014-04-11 13:09:30Z

1

The solution you are looking for is the FULL TEXT SEARCH, it'll require your DBA to create a full text index, however, once there you can perform much more powerful searches than just character pattern matching.

As the others have suggested, you could also just have an Alias table, which contains all possible forms of a team name and reference that. depending on how your search is working, that may well be the path of least resistance.

answered Apr 11, 2014 at 13:09

BanksySan

28.7k36 gold badges128 silver badges231 bronze badges

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

Comments

karlbarbour · Accepted Answer · 2014-04-11 13:09:17Z

0

Finally, and this might be better put in another question, I would like not to have to search for the correct team when the way a team is named is repeated, i.e. I would like to store alternative spellings/aliases for teams in order to find the correct team entry quickly. Can anybody advise on how I might approach this? Thank

I would personally have a team table and a teamalias table. Use relationships to marry them up.

answered Apr 11, 2014 at 13:09

karlbarbour

3522 silver badges11 bronze badges

Comments

heinkasner · Accepted Answer · 2014-04-11 13:09:53Z

0

I believe the best way to prevent this, is to have a list of teams names displayed in a dropdown list. This will also let you drop validation for the team name. The users can now only choose one set team name and will also make it much easier for you working in your database. then you can look for the exact team name as it appears in your database. i.e.:

SELECT TeamName FROM Team WHERE TeamName = [dropdownlist_name];

answered Apr 11, 2014 at 13:09

heinkasner

4356 silver badges18 bronze badges

Collectives™ on Stack Overflow

Using LIKE in SQL Server to identify strings

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related