Finding Countries that the capital have the countries name in it

Question

How can I find the countries where the capital name includes the name of the country.

ex:

Country   Capital
--------------------
A         Acity
B         B
C         xxx
D         capital of D

The answer would return A,B and D

I have tried this, but is only returning country B

SELECT name, capital
  FROM world
 WHERE name LIKE capital

This question is the thirteenth question from Sqlzoo

Answer 1

You need to use it's correct concatenation syntax.

SELECT name, capital
  FROM world
 WHERE capital LIKE concat('%',name,'%')

Note the below query is correct for most DBMS

SELECT name, capital
FROM world
WHERE name LIKE '%' + capital + '%'

But ill raise an error at Sqlzoo

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '+ capital + '%'' at line 3