Using your data, I handled one case:
declare @ table (baddata varchar(8000))
insert into @ values
('506 N Kenneth J Expressway Ste 1 Ste 1 Ste 1 Ste 1'),
('2678 NC 89 E Highway Ste 1 Ste 1 '),
('313 Avenue W S Te Ste 1 Ste 1 '),
('579 W Heritage Pk Boulevard Ste 1 Ste 1 '),
('6260 W Indian School Rd Ste 1 Ste 1 '),
('N113W1528 Montgomery Dr Apt 2 Apt 2 Apt 2 '),
('W201N1656 Hemlock St Apt 2 Apt 2 Apt 2 '),
('W201N1656 Hemlock St Apt 2 Apt 2 Apt 2 '),
('10549 Valparaiso St Apt 2 Apt 2 Apt 2 '),
('655 Walnut St Apt 2 Apt 2 Apt 2 '),
('48-425 Kilauea Ave Apt 2 Apt 2 Apt 2 '),
('N8961 Holmes Rd Apt 2 Apt 2 Apt 2 '),
('9390 Ben C Pratt 6 Mill Cypr Ste 2 Ste 2 '),
('235 Blaine Harbor Mar Gate Ste 2 Ste 2 '),
('2100 Martin Luther Jr Way Ste 2 Ste 2 '),
('600 N Wolfe Street Park Ste 2 Ste 2 ')
update @
set baddata = rtrim(baddata)
select baddata, substring(baddata, 1,n-1) gooddata
from @
cross apply (select patindex('%ste %', baddata)) _1(ste)
cross apply (select ste + patindex('%[0-9]%', substring(baddata, _1.ste, len(baddata)))) _2(n)
cross apply (select n + patindex('%[^0-9]%', substring(baddata, n, len(baddata)))) _3(not_n)
where baddata like '%ste%ste%'
However, you will need to do this once per bad pattern. Also what if you have an address like:
'1 main st fl fl 1'
That is FL as in Florida and FL again as in FLOOR?