代码之家 › 专栏 › 技术社区 › Matt

PostgreSQL-从字符串中获取第n次出现后的文本并对其编号

common-table-expression postgresql sql

Matt · 技术社区 · 6 年前

我有一个只有一个字段的小表,我想在每次出现 XX

我的表格:

MYFIELD
The quick XX brown fox XX jumps over the XX lazy dog
Get text XX after each XX nth XX occurrence XX from string

期望输出:

MYFIELD                                                     OCC   FIELDOUTPUT
The quick XX brown fox XX jumps over the XX lazy dog        1     brown  
The quick XX brown fox XX jumps over the XX lazy dog        2     jumps
The quick XX brown fox XX jumps over the XX lazy dog        3     lazy
Get text XX after each XX nth XX occurrence XX from string  1     after
Get text XX after each XX nth XX occurrence XX from string  2     nth
Get text XX after each XX nth XX occurrence XX from string  3     occurrence 
Get text XX after each XX nth XX occurrence XX from string  4    from

SQL小提琴: http://sqlfiddle.com/#!15/991c8d

2 回复 | 直到 6 年前

Matt 6 年前

WITH dummy_values AS (
  SELECT 1 UNION ALL 
  SELECT 1 UNION ALL
  SELECT 1
)
, seq AS (
  SELECT (ROW_NUMBER() OVER ())::INT occ 
  FROM dummy_values d1, dummy_values d2, dummy_values d3
)
SELECT 
  "MYFIELD"
, occ
, REGEXP_REPLACE(REGEXP_SUBSTR("MYFIELD", 'XX \\S+', 1, occ), 'XX ', '') fieldoutput
FROM mytable
JOIN seq ON occ <= REGEXP_COUNT("MYFIELD", 'XX ')

Lukasz Szozda 6 年前

你可以用它来劈开绳子 ORDINALITY

WITH cte AS (
  SELECT *
  FROM MyTABLE, regexp_split_to_table(MYFIELD, E'\\s+') WITH ORDINALITY s(c,rn)
), cte2 AS (
  SELECT myfield, c, LEAD(c) OVER(PARTITION BY MYFIELD ORDER BY rn) AS FieldOutput, rn 
  FROM cte
)
SELECT MYFIELD, Fieldoutput,
       ROW_NUMBER() OVER(PARTITION BY MYFIELD ORDER BY rn) AS occ
FROM cte2
WHERE c = 'XX'
ORDER BY MYFIELD,rn;

db<>fiddle demo