正則運算式從復雜字串中提取數字（格式xxxxx.yy-不超過兩位小數）-PostgreSQL

對此的要求回答這個問題出現在這里它僅被部分完成，至少從我的POV。

對于所有表和資料 SQL 測驗工具，請參閱此處的小提琴。

我有一個表和資料如下：

CREATE TABLE payment (id INT, amount TEXT NOT NULL);

和

INSERT INTO payment VALUES                          -- Desired result

(1, 'KES 0.80__asdfa   .80..98..00sadf   '),        --  0.80 or .8 or .80
(2, '00 a 0..0...00asafd 0000013..0.85...0000'),    --  0.85   or .85
(3, '0sf00..0...0 00.000 X013....12.851...0000'),   --  12.85
(4, '000..0...007600.00 0013..12.....0000'),        --  7600
(5, '0afs0 sdff 00...00000.56.....000343..343.0'),  --  0.56 or .56
(6, 'fs0 sdff 00...0.000710x00.56..asfd0003..3.0'); --  0.56 or .56, NOT 0.00071 or 710 

好的，所以我想提取 xxxx.yy 形式的有效數字，其中 x 的數量是任意的，而 y 的數量最多為 2（但能夠指定 #x 將是錦上添花）。

要求說明：

• 1 - 0.80- 應該是不言而喻的 - 它是第一個有效數字，小數點后有 2 位數字。
• 2 -0.85而不是13因為13..它不是一個有效數字 - 如果它是13.00（或其他兩個數字），那么可以，但不是13.0.例如。
• 3 -12.85而不是12.851因為 2 y 規則。
• 4 -7600相當明顯。但是7,600將無效 - 沒有分隔符。
• 5 - 0.56- 相當明顯。
• 6 - 現在，這將把小麥與谷殼分開......它必須是0.56而不是0.00071（2 y's），但是，它也不能710因為710x無效。有效數字是一串數字、一個小數點，然后是至少兩個（或更多）應截斷的有效數字。

截斷規則不是絕對的——我可以用PostgreSQL來舍入！

只是為了幫助您:-)，我已經包含了我的答案中的小提琴- 我的正則運算式是：

SELECT
  REGEXP_REPLACE(amount, '[^0-9\.] |\.  |\.0|\.{2,}', '', 'g')
FROM payment;

這個問題的樣本資料在底部，也是在這里的第二個小提琴上。我覺得我的正則運算式的問題是它太“量身定制”了——有太多的管道 ( |) 來說明細節。我想要更通用的東西 - 因此這個問題。

需要注意的幾點：

• 我在小提琴中留下了一個“測驗工具”，以便可以檢查解決方案。我這樣做是因為在上一個正則運算式問題中，在我們意識到PostgreSQL 的正則運算式實作存在“怪癖”導致無法回答之前，我將可憐的Wiktor Stribi?ew（非常樂于助人的家伙！）發送到各處，特別是：.Lookahead and lookbehind constraints cannot contain back references (see Section 9.7.3.3), and all parentheses within them are considered non-capturing

  我不認為這適用于這種情況，但我不想要的是從一個正則運算式站點到另一個帶有作業代碼的站點，然后發現它在 PostgreSQL 上不起作用 - 所以為了節省浪費任何人的時間，我設定了它，以便您可以將代碼放入指定的模式區域并運行 PostgreSQL fiddle？

• 關于 PostgreSQLREGEXP_REPLACE()功能的注釋。

  REGEXP_REPLACE（源，模式，替換[，標志]）。

  我使用的替換是''- 即空字串，我使用了'g'標志（全域）。如果該標志不存在，則它只執行第一次出現。這可能對任何考慮進行兩次傳球或其他解決方案的人有用 - 知道它不會受到傷害。

• 我實際上對學習如何使用正則運算式很感興趣。查看這個小提琴中的最后一個片段，看看我在開始走正則運算式之前做了什么扭曲 - 30 行到 3 行正則運算式。所以，如果你能評論你的正則運算式并逐步解釋它的作用，那就太好了，謝謝。

• 最后，任何（對我自己原始小提琴正則運算式的小改進/評論表示贊賞 - 如果你有時間！你認為有幫助的其他任何東西都會很棒！

我已經努力徹底解決這個問題，我希望這個問題是水晶的！然而，這已經夠棘手了，所以如果你需要我的任何進一步輸入（尤其是在 PostgreSQL 方面），我很樂意提供任何進一步的必要說明！

============================================

用于作業正則運算式的示例（更簡單）資料（此處為小提琴）：

INSERT INTO payment VALUES
(1,  'KES 0.80'),             
(2,  'KES  0.80'),            
(3,  'KES .80'),           
(4,  'xyzKES 0.80'),          
(5,  'KES . 0.80'),           
(6,  'KES 0.80afasf'),          
(7,  'KES 0.80__asdfa   ..'),
(8,  'KES 0.80..asdfasdf'),
(9,  'KES 0.8.0..asdfasdf'),
(10, 'KES 0.8.0    ..asdfasdf...');

uj5u.com熱心網友回復：

這不是純粹基于正則運算式的解決方案，但它確實產生了所需的輸出：

查詢#1

select distinct on (id) -- 5
  id
, amount
, mat
, cast(mat as numeric (10, 2)) mat_num -- 6
from payment
join lateral (
  select 
    array_to_string(_mat, '') mat -- 2
  , rn
  from regexp_matches(amount, '\d \.\d{1,2}', 'g') -- 1
       with ordinality as x(_mat, rn) -- 3 
) x on true
where mat !~ '^0 \.0 $' -- 4
order by id, rn; -- 5

在 DB Fiddle 上查看

這里發生了什么：

1. 使用帶有標志 'g' 的函式 regexp_matches 來提取所有匹配的子字串<digit>.<digit>{1, 2}。請注意，如果它沒有十進制數字，這將不匹配子字串。我不確定這對您是否重要，但是您示例中的所有匹配項似乎都有小數點。
2. regexp_matches 為每個匹配回傳多行，其中每一行是匹配組的陣列。因為我們只有 1 個組，所以這個陣列總是有 1 個元素。有必要將陣列轉換回字串以進行進一步處理。
3. 為匹配分配一個序列 id 也很重要，因為我們希望保留第一個有效匹配，因為我們不希望排序未定義。1、2 和 3 都在橫向連接查詢中指定
4. 因為我們的正則運算式模式也會匹配00.00等等，所以通過 where 子句擺脫這些很重要
5. 選擇第一個有效匹配，distinct on 與按匹配序列 ( rn)排序的行一起使用
6. 將匹配的字串轉換為數字可以去掉任何前導或尾隨的 0。

標籤：正则表达式 细绳 PostgreSQL的

上一篇：如何在pl/pgsql中宣告具有特定列名的行文字？

下一篇：如何在postgres的jsonb中獲取屬性的長度？