我有以下地址,只需要去掉实际的街道名称
示例数据:
DECLARE @T AS TABLE
(
Address varchar(100)
)
INSERT INTO @T (Address, Desired) VALUES
('Unit 5, 10-16 Smith Street'),
('180 Long Hill Road'),
('318 Great East Build Road'),
('50 Angus Street'),
('103 Inch Street'),
('Unit 1, 28A State Road')
预期结果:
Smith Street
Long Hill Road
Great East Build Road
Angus Street
Inch Street
State Road
如何做到这一点呢?
到目前为止,我已经有了这个,它就快到了,但它返回的是Road as Roa and Street和as Street:
SELECT SUBSTRING(
TRIM(
REPLACE(
SUBSTRING(
@String,
CHARINDEX(',', @String),
LEN(@String)
),
',',
'')
),
CHARINDEX(' ',
TRIM(
REPLACE(
SUBSTRING(
@String,
CHARINDEX(',', @String),
LEN(@String)
)
, ',', '')
)
) +0,
205
)
发布于 2018-10-16 14:21:41
select street into #tab from (
select 'unit 5, 10-16 Smith Street' as street
union all select '180 Long Hill Street'
union all select '103 Inch Street') t
select
reverse(rev) street,
reverse(substring(rev,1,PATINDEX('%[^0-9][0-9]%',rev)))
from
(select reverse(street) as rev from #tab) t
drop table #tab
https://stackoverflow.com/questions/52828721
复制相似问题