SQL Query to SELECT strings which are either words

Question

I am working on the following query:Find the country where the capital is the country plus "City". Hopefully, you can infer what I mean:

The following query will solve such:

SELECT world.name
  FROM world
WHERE world.capital LIKE CONCAT(world.name, " City")

Now, say that I also wanted to match for another word, Town. We could do something like this:

SELECT world.name
  FROM world
WHERE world.capital LIKE CONCAT(world.name, " City") OR world.capital LIKE CONCAT(world.name, " Town")

Can I condense the above statement to only one LIKE clause? Say something like this?

SELECT world.name
 FROM world
WHERE world.capital LIKE CONCAT(world.name, " [City,Town]")

I just want a more terse SQL statement

Which RDBMS is this for? Please add a tag to specify whether you're using mysql, postgresql, sql-server, oracle or db2 - or something else entirely. — marc_s
– marc_s, Commented Mar 6, 2016 at 16:22

Gordon Linoff · Accepted Answer · 2016-03-06 16:16:55Z

1

Doing what you want requires regular expressions, whose support depends on the database. You cannot easily do this with LIKE.

Because "city" and "town" have the same number of letters and most databases support right(), you could do:

where world.capital like concat(world.name, ' ____') and
      right(world.capital, 4) in ('City', 'Town')

I think, however, that the method with two likes or using regular expressions is cleaner.

answered Mar 6, 2016 at 16:16

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Hmm, that looks pretty nasty. Is there any solution here?

@zero . . . Your original approach is fine.