SQL reverse LIKE

Question

I have a table holding a list of countries. Say one of these countries is 'Macedonia'

What SQL query would return the 'Macedonia' record if a search is made for 'Republic of Macedonia'?

I believe that in linq it would be something like

var countryToSearch = "Republic of Macedonia";

var result =  from c in Countries
              where countryToSearch.Contains(c.cName) 
              select c;

Now what would the SQL equivalent for the query above be?

Had it been the other way round (i.e. the database has the long version of the country name stored) the below query should work:

Select * from country
where country.Name LIKE (*Macedonia*)

but I do not see how I can reverse it.

Side note: the country names in the table will always be the short version of country names

Answer 1

Just reverse the operator (and fix the syntax)

Select * from country
where 'Republic of Macedonia' LIKE CONCAT('%',country.Name,'%')

Answer 2

You can use CHARINDEX for this.

Select * from country
where CHARINDEX(country.Name, 'Republic of Macedonia') > 0

Answer 3

For SQLite, a substring (mysubstring column) in a table (mytable) can be compared against an arbitrary larger test string:

select * from mytable where instr('Somewhere substring is here', mytable.mySubstring) > 0