Also late to the party; have you considered stripping the odd text elements out of the column to leave the city name? You'd want to do this as a one-off.
DROP table #import;create table #import (importcity nvarchar(128), title nvarchar(128))
insert #import (importcity, title)
values
('SomthingElse', 'PRN - Concord, NC'),
('SomthingElse', 'PRN - San Jose,NM'),
('SomthingElse', 'SLP - PRN - San Jose,NM'),
('SomthingElse', 'San Jose, NM - PT - PRN'),
('SomthingElse', 'PTA - PRN - Santa Cruz,NM'),
('SomthingElse', 'Rancho Santa Margarita, CA PT - PRN'),
('SomthingElse', 'PT - PRN-San Diego, CA'),
('SomthingElse', 'OT - PRN - Rancho Palos Verdes,CA'),
('SomthingElse', 'SLP - PRN - Rancho Santa Margarita, CA'),
('SomthingElse', 'OTR - PRN - Laguna Hills,CA'),
('SomthingElse', 'PT - PRN - Cardiff by the Sea,CA')
SELECT *
FROM #import
CROSS APPLY (SELECT CleanedTitle =
LTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(title,'OTR - PRN',''),'OT - PRN',''),'PTA - PRN',''),'SLP - PRN',''),'PT - PRN',''),'PRN -',''),'-',''))
) x
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden