Get data replacing text between braces()

Question

I have a table where the location data can be in 3 different formats like mentioned below

1. United States (US);New York (NY)
2. United States (US);New York
3. United States;New York

I have to pull all the records with all the 3 formats mentioned above.
So, I have tried replacing the content in braces with empty and then started comparing.

The query is

select * from TaxonomyLocations_Archive 
where REPLACE(REPLACE(TaxonomyHierarchy, 
SUBSTRING(TaxonomyHierarchy, 
CHARINDEX('(', TaxonomyHierarchy), 
CHARINDEX(')', TaxonomyHierarchy) - 
CHARINDEX('(', TaxonomyHierarchy) + 1), ''), 
' ;', ';') ='United States;New York'

But the above query is only comparing data till the first occurrence of the brackets and only returning matches with the 2nd and 3rd formats [United States (US);New York and United States;New York].

please suggest me the changes that I have to do to make it work for the 1st format [United States (US);New York (NY)] as well.

Answer 1

Variant:

select * from TaxonomyLocations_Archive 
where TaxonomyHierarchy like 'United States%' AND TaxonomyHierarchy like '%;New York%'