CODEWITHSUNDEEP
sqlsql-serversql-like
Steve W
Steve W

Reputation: 1128

Using LIKE in SQL Server to identify strings

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

Upvotes: 2

Views: 157

Answers (3)

heinkasner
heinkasner

Reputation: 425

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];

Upvotes: 0

BanksySan
BanksySan

Reputation: 28500

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.

Upvotes: 1

karlbarbour
karlbarbour

Reputation: 352

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.

Upvotes: 0

Related Questions