CODEWITHSUNDEEP
mysql
Brian
Brian

Reputation: 27392

MySQL Select names with last names starting with certain letter

I have a MySQL database with a field Name which contains full names. To select all people with last names starting with a particular letter, let's say A for this example, I use the following query: SELECT * FROM db WHERE Name LIKE '% A%'. However, this also selects users who have a middle name starting with A. Is there anyway to alter this query so that only a last name starting in A will be selected?

Upvotes: 1

Views: 6424

Answers (4)

bibsbarreto
bibsbarreto

Reputation: 371

SELECT * FROM db WHERE Name REGEXP '^A' also works!

Upvotes: 0

None
None

Reputation: 5649

I recently had the same situation. The optimum solution is to not store names in the database as full names, however, sometimes we are not in control of the data and need a solution. I ended up using the following:

SELECT * FROM db WHERE Name REGEXP " +[[:<:]]j[-'a-z]{3,}$| +[[:<:]]j[-'a-z]{3,} jr.$| +[[:<:]]j[-'a-z]{3,} sr.$";

This will search for last names starting with 'J'. I found J and S to be the most difficult because some names in the database include Jr. or Sr. at the end, throwing off the query.

I have to mention that this is a very rough solution and I can think of many instances when it would not work. It could get very long very quick. It accounts for hyphenated names or names with apostrophes, but there is no sure-fire way to match names.

A brief explanation of what is going on:

  • The first space makes sure we are never matching against the first name.
  • [[:<:]] is the mysql beginning of a word boundary ([[:>:]] would match the end of a word). So [[:<:]]j matches words starting with J. Remember mysql is case insensitive when matching strings.
  • $ makes sure that it is the end of the string since we are looking for last names. This presents a problem with Jr or Jr. and Sr or Sr. so {3,} makes sure that it is longer than 2 letters and [-'a-z] doesn't allow periods. I realize this will skip valid 2 letter last names such as Xu, but I have more Jrs than Xus in the database. An alternative solution would be to replace {3,} with *. Then two letter names would be returned, but if Jr is missing the period it will return as well.
  • Finally this expression is run with two other cases, ' jr.$' and ' sr.$' so that the Jr. and Sr. results are returned when the last name matches the letter being searched for.

Additional cases such as II or III could also be added.

You can also use the MySQL REGEXP documentation as a reference.

Upvotes: 1

AvatarKava
AvatarKava

Reputation: 15443

Ignacio's Regex works and is a lot more flexible - you could also use something like this if Regex completely confuses you:

SELECT * FROM db WHERE LEFT(SUBSTRING_INDEX(Name, ' ', -1), 1) = 'A'

http://dev.mysql.com/doc/refman/5.1/en/string-functions.html

Upvotes: 2

Ignacio Vazquez-Abrams
Ignacio Vazquez-Abrams

Reputation: 798556

SELECT * FROM db WHERE Name REGEX ' A[-[:alnum:]'']*$'

Upvotes: 4

Related Questions