Skip to content
Advertisement

Select rows where the string in one column is an extension of a string in another column

The question: Find the capital and the name where the capital is an extension of name of the country. Note that the capital must be longer than the name (Mexico-City compared to Luxembourg) This is my work, but it’s incorrect

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

Advertisement

Answer

To get the extension you can use REPLACE function which in following query replacing the ‘name’ of country from ‘capital’ to null.

SELECT name , REPLACE(capital,name,'')
FROM world
WHERE capital LIKE concat(name, '_%');
6 People found this is helpful
Advertisement