时间戳

首页 > 解决方案 > 选择邮政编码并放入新列

sql - 选择邮政编码并放入新列

问题描述

我有一个 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

谢谢您的帮助！

标签： sqlsql-server

解决方案

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

一旦你有了这个位置，一个 SUBSTRING 就可以得到它。

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 的记录的子句，则PATINDEX在没有 ZipCode 时将返回 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;

对 reextester 的测试在这里

推荐阅读