选择zipcode并放入新列

Question

我有一个sql表，其中包含一个包含大量信息的列。从本专栏中，我只需要邮政编码。我可以选择包含邮政编码的行：

SELECT *
FROM Flow
Where Title LIKE '% [0-9] [0-9] [0-9] [0-9] [A-Z] [A-Z]%'

示例数据：

B2 (Use Ambu: b-ride care) 13401 Rit 24530 Amsterdam Linnaeusstraat 1093EN Basic team Oost-Watergraafsmeer
B2 Outpatient clinic Urology Route 23 Wagnerlaan Arnhem 6815AD 17143
B2 (Use Ambu: b-ride care) 13401 Rit 24531 Amsterdam Vrolikstraat 1092TX
B2 Outpatient clinic Urology Route 23 Wagnerlaan Arnhem 6815AD 17143
A2 Weth R A Wieggersstraat Giesbeek 6987BD 17145

但是现在我只想把这些行中的邮政编码放在另一列中，并使用名称邮政编码。

结果：

ZipCode
1093EN
6815AD
1092TX
6815AD
6987BD

谢谢你的帮助！

Answer 1

PATINDEX可以用与LIKE相同的语法找到这种模式的位置。

一旦你有了这个位置，一个超能力就能得到它。

SELECT *, 
 SUBSTRING(Title, PATINDEX('%[^0-9A-Z][0-9][0-9][0-9][0-9][A-Z][A-Z][^0-9A-Z]%', Title+' ')+1, 6) AS ZipCode
FROM Flow
WHERE Title+' ' LIKE '%[^0-9A-Z][0-9][0-9][0-9][0-9][A-Z][A-Z][^0-9A-Z]%'

如果没有过滤具有WHERE的记录的ZipCode子句，则当没有ZipCode时，PATINDEX将返回0。

然后，为了避免SUBSTRING仍然会从没有它的字符串中返回一些东西，最好将它包装在一个CASE中。

SELECT *, 
(CASE
 WHEN PATINDEX('%[^0-9A-Z][0-9][0-9][0-9][0-9][A-Z][A-Z][^0-9A-Z]%', Title+' ') > 0
  THEN SUBSTRING(Title, PATINDEX('%[^0-9A-Z][0-9][0-9][0-9][0-9][A-Z][A-Z][^0-9A-Z]%', Title+' ')+1, 6) 
  END) AS ZipCode
FROM Flow

额外：

关于另一张桌子。

下面是一个使用临时表演示的示例片段：

-- Using a temporary table for the example
CREATE TABLE #Flow (
  Id int primary key identity(1,1) not null,
  Col1 VARCHAR(8) not null,
  Title NVARCHAR(100) not null,
  ZipCode VARCHAR(6)
);

-- Sample data
INSERT INTO #Flow (Col1, Title) VALUES
('B2X1', N'B2 (Use Ambu: b-ride care) 13401 Rit 24531 Amsterdam Vrolikstraat 1092TX'),
('B2X2', N'B2 Outpatient clinic Urology Route 23 Wagnerlaan Arnhem 6815AD 17143'),
('A2Y1', N'A2 Weth R A Wieggersstraat Giesbeek 6987BD 17145');

 CREATE TABLE #OtherTable (
  Id int primary key identity(1,1) not null,
  OtherCol1 VARCHAR(8) not null,
  FlowId INT,
  ZipCode VARCHAR(6)
);

-- update the ZipCode in the origin table
UPDATE  #Flow
SET ZipCode = SUBSTRING(Title, PATINDEX('%[^0-9A-Z][0-9][0-9][0-9][0-9][A-Z][A-Z][^0-9A-Z]%', Title+' ')+1, 6) 
WHERE ZipCode IS NULL
   AND PATINDEX('%[^0-9A-Z][0-9][0-9][0-9][0-9][A-Z][A-Z][^0-9A-Z]%', Title+' ') > 0;

SELECT * FROM  #Flow;

INSERT INTO #OtherTable (OtherCol1, FlowId, ZipCode)
SELECT f.Col1, f.Id, f.ZipCode
FROM #Flow f
ORDER BY f.Col1 DESC, f.Id ASC;

SELECT * FROM  #OtherTable;

基于rextester的这里测试

Answer 2

备选方案：

SELECT *, 
 SUBSTRING(yazi, PATINDEX('%[0-9][0-9][0-9][0-9][A-Z][A-Z]%', yazi), 6) AS ZipCode
FROM #Onder