Postgresql 在文本中搜索 6 位数字

为了解决您的问题，我执行了以下操作（下面的所有代码都可以在此处的小提琴中找到）。该代码使用SUBSTRING()函数 ( manual ) 和波浪号( ~) 正则表达式 (regex) 匹配运算符 ( manual )。This是一个很好的正则表达式站点（快速开始这里）。

CREATE TABLE test 
(
  s1_no INTEGER GENERATED ALWAYS AS identity,
  address TEXT NOT NULL
);

然后使用您的数据（以及我自己的一些测试数据）进行填充。顺便说一句，所有应该是 12 位长的 PIN 在您的数据中实际上是 11 位，所以我假设 11 是正确的位数 - 根据需要更改：

INSERT INTO test (address) 
VALUES 

--
-- my own test cases
--

('"sdfg ghhjbghjj 123456 12345678901"'),
('"dsdcfg vbggff 12345 123456789"'),      --  <<---- should NOT be returned
                                          --  no 11 digit number
--
-- your data (with concatenated records corrected)
--

('"16/127, Off M G Road, Goregaon (west) Mumbai, Maharashtra, 400062 02228744298"'),
('"113, 113,ragiptblr-2, Ragi Pet Bangalore, Karnataka, 560002 2229218"'),
('"2, 2, Panchal Estate, Bapunagar Ahmedabad, Gujarat, 380024 07922740143"'),
('"2nd Flr, B Wing, Jay Chamber, Service Rd, Near Telephone Exchange, Vile Parle (east) Mumbai, Maharashtra, 400057 02226104168"'),
('"New#27, Gopalakrishna Street, T Nagar Chennai, Tamil Nadu, 600017 04428150211"'),
('"9360 61/8, Multani Dhanda, Pahar Ganj Delhi, Delhi, 110055 01123550815"'),
('"113, 113,ragiptblr-2, Ragi Pet Bangalore, Karnataka,  2229218"'),

--
-- This last record from your own data, returns NULL for sstr (see below)
-- because there is no 6 digit no. in the address.
--

('"9360 61/8, Multani Dhanda, Pahar Ganj Delhi, Delhi,  01123550815"');

然后使用查询：

SELECT
  s1_no,
  TRIM(SUBSTRING(address FROM ' \d{6} ')) AS sstr
FROM test
WHERE address ~ ' \d{11}"'
ORDER BY s1_no;

结果：

s1_no   sstr
1   123456
3   400062
5   380024
6   400057
7   600017
8   110055
10  NULL    
    7 rows 

记录 2、4 和 9 未返回，因为没有 11 位 PIN（无法找出它的用途），记录 10 返回NULL，因为没有6 digit PIN. 该TRIM()函数 ( manual ) 删除正则表达式返回的空格。

正则表达式的解释：

该SUBSTRING()函数提取与模式匹配的字符串。

' \d{6} '

• 匹配一个空格，

• 后跟 6 位数字。( ) 是表示单个数字\d的“速记字符类”（参见此处）。

• 花括号充当正则表达式中的重复量词。它们之间的单个 6 表示完全匹配 6 次。例如，可以放置类似 {3,6} 的内容，它会匹配 3 到 6 次出现。

• 最后一个空格意味着这 6 位数字后面还必须跟一个空格才能进行匹配。

波浪号 ( ~) 运算符可以看作是英文单词“contains”的同义词。

• ~ ' \d{11}"'- 表达式是否包含空格，后跟正好11 位数字，后跟双引号 ( ")？

您可以NULL通过使用（见小提琴）消除：

SELECT
  s1_no,
  TRIM(SUBSTRING(address FROM ' \d{6} ')) AS sstr
FROM test
WHERE SUBSTRING(address FROM ' \d{6} ') IS NOT NULL 
AND address ~ ' \d{11}"';

最后，您的PIN将以. 这没关系，因为不使用 PIN 进行加、减、乘或除。TEXTINTEGER

但是，您是否希望将它们散列为INTEGERs 以用于分区或类似用途。您可以使用非常有用的[PG_TYPEOF()][10]函数（manual）返回任何表达式的类型，如下所示（也在小提琴中）：

SELECT
  s1_no,
  TRIM(SUBSTRING(address FROM ' \d{6} ')) AS sstr,
  PG_TYPEOF(TRIM(SUBSTRING(address FROM ' \d{6} ')))
FROM test
WHERE address ~ ' \d{11}"'
ORDER BY s1_no;

结果（为简洁起见）：

s1_no   sstr    pg_typeof
1       123456       text
3       400062       text
...
...
...

出于散列的目的，您可以按如下方式使用非标准 PostgreSQL CAST() operator ::（手册）（参见小提琴）：

TRIM(SUBSTRING(address FROM ' \d{6} '))::INT

如果您希望按范围而不是列表对 PIN 进行分区，这也可能会有所帮助。