3

This is the table I am working with...

enter image description here

I am trying to "Find the countries that have three or more a in the name".

Here is my current code...

SELECT name FROM world
  WHERE name LIKE '%aaa%'

world contains name and continent.

Am I even close?

Barmar
  • 741,623
  • 53
  • 500
  • 612
Erik Åsland
  • 9,542
  • 10
  • 30
  • 58

1 Answers1

6

Your initial attempt will only match countries that have three adjacent as in the name. Try the following:

SELECT name FROM world
  WHERE name LIKE '%a%a%a%'

The character a matches only the character a (case sensitivity depends on the table and server, for MS SQL at least). The character % will match any number of characters, any number of times. The seven characters in my query will mean that there are at most seven groups. For example:

Afghanistan is broken into , A, fgh,a,nist,a, and n.

Algeria is broken into , A, lgeri, a, , and then there is no remaining a to include, so it is not included as a match.

Madagascar could be broken into Mad, a, g, a, sc, a, and r. Note that the first group contains an a, because % allows any character, including a. (Here I assume the pattern matching is greedy, which means it makes the capture groups as large as possible, which prefers earlier groups to later groups.)

Community
  • 1
  • 1
Matthew Gray
  • 1,668
  • 15
  • 17
  • That works, I don't understand why though. I understand why a country with three "a"'s would come out correct, but how does this code include the "or more" part of the problem? – Erik Åsland Oct 23 '15 at 22:15
  • @ea87 Expanded the answer to explain how `%` will work for three countries, which will hopefully illustrate that. It would be more difficult to match exactly three `a`s than three or more `a`s--the simplest way would be to include a NOT LIKE to exclude four or more `a`s. – Matthew Gray Oct 23 '15 at 22:23