[SQL] How to query cases with multiple pattern matching values in the same column

Ex Table:

Name Location
John Smith NewYork1131413431
John Smith NewYork2222213
Marc White Boston111118174124712
Sarah Phillips NewYork2222123
Sarah Phillips Boston2222918

I'm trying to query only the names in which they exist in 2 or more cities (for the case of this dataset, it will almost always be 2 cities max). So from this table, only Sarah Philips should return.

I wrote a query with the 'like NewYork% or like Boston%' operators with having count(location) > 1. But doing that would return John Smith as well because the NewYork string has multiple variations. The characters after the city name are also random in length.

Continue reading...