去掉街道名称中的单词
去掉街道名称中的单词
提问于 2018-10-16 13:54:23
我有以下地址，只需要去掉实际的街道名称
示例数据：
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
)回答 1
Stack Overflow用户
发布于 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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/52828721
复制相关文章
相似问题