开发者

how to separate text from integer

开发者 https://www.devze.com 2023-02-17 14:00 出处:网络
I have addresses: ALKOŅU 3-20; M.LUBŠNAS 16V-9; STIEBRU 6-22; ANDREJA UPĪĀA IELA 16-2; MISNKAS 4 -115;

I have addresses:

ALKOŅU 3-20;
M.LUBŠNAS 16V-9;
STIEBRU 6-22;
ANDREJA UPĪĀA IELA 16-2;
MISNKAS 4 -115;
CISKADI,BAZNICAS 4;

How it is 开发者_如何转开发possible in sql to separate first text part (district) from integer (house and flat number)?


Assuming the break-point is ALWAYS the first digit, then

SELECT RTRIM(LEFT(col, PATINDEX('%[0-9]%', col + '0') -1)) as District,
       STUFF(col, 1, PATINDEX('%[0-9]%', col + '0') -1, '') as HouseAndFlat
FROM ...

e.g.

with t(col) as (
select
'ALKOŅU 3-20' union all select
'M.LUBŠNAS 16V-9' union all select
'STIEBRU 6-22' union all select
'ANDREJA UPĪĀA IELA 16-2' union all select
'MISNKAS 4 -115' union all select
'CISKADI,BAZNICAS 4')

SELECT RTRIM(LEFT(col, PATINDEX('%[0-9]%', col + '0') -1)) as District,
       STUFF(col, 1, PATINDEX('%[0-9]%', col + '0') -1, '') as HouseAndFlat
FROM t
0

精彩评论

暂无评论...
验证码 换一张
取 消