Question

I would like to be able to return all canadian postalcode which can be find in using the following regex(migth have a space like: A0A 0A0 or A0A0A0) regex [A-Z]d[A-Z]s*d[A-Z]d The problem is when there is more than 1 match that a part of it it in . I ve played with capturing group (too much) and i m more lost then at the start, oh well.

鉴于图谋: 缩略语
- I would expect the following match
  - A0A 0A0 (ok)
鉴于浮雕:SOMETEXTB1A0A0OTHERSTUFFB2A 0B0OTHER
- I would expect the following match:
  - B1A0A0 (ok)
  - A0A0A0 (not found)
  - B2A 0B0 (not found)

The regex101

这里是我为后格雷斯克的考验。

with barcodes as(
    select * 
    from
        (values
         ( AAAC9B92WLEDH0G5R0Z0|2024-01-01T01:59:39.379-05 )
         , (  SOMETEXTJ9H5G0L2J0OTHERSTUFF )
        )b(barcode)
)
select * 
from barcodes  b
left join lateral  (    
    SELECT UNNEST(
        REGEXP_MATCHES(
            b.barcode
            ,  [A-Z]d[A-Z]s*d[A-Z]d 
            ,  g 
        )
    ) match
)t on true

Answer 1

缩略语 0B0OTHER .

B1A0A0 is found.
A0A0A0 is not found because it overlaps with B1A0A0.
B2A 0B0 is found.

通常,通过在 https://stackoverflow.com/a/5616910/14660”内使用一个捕获组处理重叠问题,积极看望,如(?=([A-Z]d[A-Z]s*d[A-Z]d])。这里是demonstration 。遗憾的是,。

Look头和 look头的制约因素不能包含背书(见第9.7.3.3节),其内的所有母子都被视为非母体。

你们所有人都是空洞的。

您可尝试安装 pgpcre。这使你能够经常表达。

另一种方案拟定语言可能做得更好。将数据重新分类,并(如果你需要不止一次这样做)在数据库中重新插入分层的邮政编码。

Answer 2

你们可以直接取得你想要的结果,尽管它需要一定程度的工作。我们不是试图与整个reg子相匹配,而是仅仅与reg首的特性相对应,而是用head头 assertion言来与reg的平衡相匹配。这确保了我们找到能够与监管机构相匹配的所有地点,不论它们是否重叠。因此,我们首先使用这一规定:

[A-Z](?=d[A-Z]s*d[A-Z]d)

我们首先在regexp_count上使用,以了解在座标上有多少对应,并使用<代码>generate_series<<>/code>生成代表每个人的序列号。然后,我们使用<代码>regexp_instr,以找到每一方在座标上的起始位置。最后,我们使用<代码>regexp_substr,开端职位和原封顶,以提取对应物:

with barcodes as (
    select * 
    from
        (values
         ( SOMETEXTB1A0A0A0OTHERSTUFFB2A 0B0OTHER )
         , (  SOMETEXTJ9H5G0L2J0OTHERSTUFF )
         , (  SOMETEXTA0A 0A0OTHERSTUFF  )
        )b(barcode)
), counts as (
select *
from barcodes b
left join lateral (
  select generate_series as n
  from generate_series(1, regexp_count(barcode,  [A-Z](?=d[A-Z]s*d[A-Z]d) ))
) c on true
),
positions as (
select barcode, regexp_instr(barcode,  [A-Z](?=d[A-Z]s*d[A-Z]d) , 1, n) as start
from counts
)
select barcode, regexp_substr(barcode,  [A-Z]d[A-Z]s*d[A-Z]d , start) as postcode
from positions

产出:

barcode                                 postcode
SOMETEXTB1A0A0A0OTHERSTUFFB2A 0B0OTHER  B1A0A0
SOMETEXTB1A0A0A0OTHERSTUFFB2A 0B0OTHER  A0A0A0
SOMETEXTB1A0A0A0OTHERSTUFFB2A 0B0OTHER  B2A 0B0
SOMETEXTJ9H5G0L2J0OTHERSTUFF            J9H5G0
SOMETEXTJ9H5G0L2J0OTHERSTUFF            H5G0L2
SOMETEXTJ9H5G0L2J0OTHERSTUFF            G0L2J0
SOMETEXTA0A 0A0OTHERSTUFF               A0A 0A0

说明一单独列出步骤,以明确这项工作如何进行,你可以很容易地将一些步骤结合起来,以减少行动次数(见关于相关dle的第二次询问)。如果希望,你也可以将成果汇总到一个阵列。

Demo on dbfiddle.uk

友情链接