我有包含街道名称+户号(+可能的分隔符)(+可能的公寓号)+邮政编码+城市的地址列
5个不同的例子:
( Street ), (Postal) (City)
"Testalley 3, 00200 Helsinki"
"Testalley 3 A 21, 00200 Helsinki
"TestAlley 3 B, 00300 Helsinki
"TestAlley 3, 00500 Helsinki AS
"testAlley 3 F 22, 00500 Helsinki AS
所以，地址的变化是相当大的
我希望把这一大堆地址分成三列。
SELECT
bigAddress,
SUBSTRING(bigAddress,LEN(LEFT(bigAddress,CHARINDEX(',', bigAddress)+2)),LEN(bigAddress) - LEN(LEFT(bigAddress,CHARINDEX(',', bigAddress))) - LEN(RIGHT(bigAddress,CHARINDEX(' ', (REVERSE(bigAddress)))))) AS Postcode
FROM TABLEXX
^^这几乎适用于邮政编码。
唯一的问题是，如果城市不是像";赫尔辛基"然后是城市的邮政编码。如00300 Ylistaro(当城市为Ylistaro AS时)
with cte as (
SELECT
ID,
bigAddress,
SUBSTRING(bigAddress,LEN(LEFT(bigAddress,CHARINDEX(',', bigAddress)+2)),LEN(bigAddress) - LEN(LEFT(bigAddress,CHARINDEX(',', bigAddress))) - LEN(RIGHT(bigAddress,CHARINDEX(' ', (REVERSE(bigAddress)))))) AS Postcode,
RIGHT(bigAddress,CHARINDEX(',', (REVERSE(bigAddress))) - 1) AS City
FROM TableXXX
select
bigAddress,
LEFT(Postcode,5) As PostcodeV2,
STUFF(City, 1, 7, '') AS CityV2
FROM cte
^ ^这也是相当伟大的，它确实失败了，当试图把它放入PowerBi DirectQuery。PowerBI不支持DQ模式，导入模式也有一些其他的问题。
你要做的是非常危险的，因为这是一个众所周知的问题，没有真正正确和安全的方法将街道，邮政和城市从这样一个完整的字符串分开。所以请注意，以下只是一个想法，以帮助你，但在未来，你应该直接保存在不同的列的信息。无论如何，下面的解决方案只适用于一些假设。例如，在街道和其他地方之间必须有一个逗号。邮政不得包含任何非数字字符，城市不得包含任何数字字符。思路是首先向表中添加四列:
ALTER TABLE yourtable ADD street varchar(200);
ALTER TABLE yourtable ADD postal varchar(200);
ALTER TABLE yourtable ADD city varchar(200);
ALTER TABLE yourtable ADD prov varchar(600);
前三列应该是您将来用来保存信息的列。证明列只在数据"转换"期间使用。然后再次移除。作为第一步，您将使用逗号之前的所有内容更新street列，并使用其余内容更新prov列:
UPDATE yourtable SET street = SUBSTRING(bigAddress, 0, charindex(',', bigAddress, 0)),
prov = REPLACE(SUBSTRING(bigAddress,CHARINDEX(',',bigAddress) + 1, LEN(bigAddress)),' ','');
然后用当前保存在prove列中的从第一个非数字字符开始的整个字符串填充city列。换句话说，您将从城市中删除邮政:
UPDATE yourtable SET
city = RIGHT(prov,LEN(prov) - (PATINDEX('%[^0-9]%',prov) -1));
之后，您将从prove列中删除城市，以获得邮政信息，并将其保存在邮政列中:
UPDATE yourtable SET postal = REPLACE(prov, city,'');
这三个列现在被正确填充了(正如我所说，只要满足所需的条件)，因此您可以再次删除prov列:
ALTER TABLE yourtable DROP COLUMN prov;
我创建了一个示例来显示这是正确的:db<>fiddle
以后，请不要这样做，而是使用单独的列。
考虑邮政编码为固定长度的5位数字，您可以使用CHARINDEX,SUBSTRING,LEFT和RIGHT加上一些常数来获得数据:
CREATE TABLE addresses (
address VARCHAR(50) NOT NULL
);
INSERT INTO addresses (address)
VALUES
('Testalley 3, 00200 Helsinki'),
('Testalley 3 A 21, 00200 Helsinki'),
('TestAlley 3 B, 00300 Helsinki'),
('TestAlley 3, 00500 Helsinki AS'),
('testAlley 3 F 22, 00500 Helsinki AS');
SELECT
LEFT(address, CHARINDEX(',', address) - 1) AS street,
SUBSTRING(address, CHARINDEX(',', address) + 2, 5) AS postcode,
RIGHT(address, LEN(address) - CHARINDEX(',', address) - 7) AS city
FROM addresses;
结果: