Postgresql

如何替換字元串（文本列）中最後一次出現的字元？

  • December 1, 2021

假設我在 Postgres 中有一個具有此值的文本欄位：

'bar$foo$john$doe$xxx'

我想將最後一次出現的美元 ( $) 字元替換為另一個字元，例如“-”。替換後，該欄位的內容應為：

'bar$foo$john$doe-xxx'

介紹：

這個問題涉及到一些橫向思考。任何字元的最後一次****出現也是該字元在反轉時字元串中的第一次出現！所有的解決方案（一欄）都使用這種方法。

對於提出的所有 5 個解決方案，我們有以下內容（下面的所有程式碼的小提琴都可以在這裡找到。每個解決方案的單獨小提琴都包含在下面的每個解決方案中）：

CREATE TABLE test
(
 id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
 t_field TEXT
);

PRIMARY KEY只有第 5 個解決方案需要。然後我們執行以下查詢來填充表——第一條記錄是 OP 自己的數據——其餘的都是隨機生成的！

在：

INSERT INTO test (t_field)
VALUES ('bar$foo$john$doe$xxx');  -- OP's own data

隨機數據：

INSERT INTO test (t_field)
SELECT 
 LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '$' ||
 LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '$' ||
 LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '$' ||
 LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT) || '$' ||
 LEFT(MD5(RANDOM()::TEXT), FLOOR(RANDOM() * (5 - 3 + 1) + 3)::INT)
FROM 
 GENERATE_SERIES(1, 29999);  --<<== Vary here
  
--
-- For this fiddle, we only have 30,000 (29,999 + the OP's original datum) records 
-- (although relative magnitudes appear good), the individual fiddles use
-- 300,000. 
--
-- 300,000 appears large enough to give reliable consistent results and small 
-- enough so that the fiddle doesn't fail too often - rarely fails on 30k.
--
--
-- You can vary this number, but please consider using the individual fiddles for
-- large numbers of records so as not to hit the db<>fiddle server too hard!
--
-- The home test VM used 10,000,000 records - 16 GB RAM, 1 CPU, SSD
--

解決方案將按性能排序。它在 db<>fiddle 和家庭虛擬機（16GB RAM，SSD）上進行了測試，測試表中有 10M 記錄 - 不要在 fiddle 上嘗試 10M！每種方法都給出了一個因素，即它比 VM 上最快的方法花費了多長時間。

在所有情況下，bar$foo$john$doe-xxx對於 OP 的原始數據和測試查詢（LIMIT 2顯示它們的行為符合預期 - 即用$連字元 ( ) 替換最後一個美元 ( ) 符號） ，都可以獲得所需的結果-。您可以在小提琴檢查。

1：Postgresql 字元串函式（參見手冊）、使用OVERLAY()、STRPOS()AND REVERSE()（個人小提琴）：

SELECT
 t_field, 
 OVERLAY(t_field PLACING '-' 
   FROM 
     LENGTH(t_field) + 1 - STRPOS(REVERSE(t_field), '$')
        ) AS result
FROM test;
  • 性能：10M 記錄的時間 = 8034.787 ms。
  • 與最快的比較 = 1.0 x

2： Reverse() 和正則表達式函式REGEXP_REPLACE()（個人小提琴）：

SELECT 
 REVERSE(REGEXP_REPLACE(REVERSE(t_field), '\$', '-'))
FROM
 test;

正在做的事情（從內到外）是：

請注意，這只會替換第一個實例，$因為'g'(global) 標誌不存在 - 如果程式碼 read ... , '-', 'g')，則所有美元都將被替換 - 無論如何你都可以使用（便宜得多）REPLACE()函式來做到這一點。

另請注意，這$是一個正則表達式meta-character- 即它在正則表達式中具有特殊功能（它表示字元串的最後一個字元），因此\在替換它時必須使用反斜杠 ( ) 字元對其進行轉義。

  • 然後，最後一步是將我們編輯的字元串反轉回原來的順序，我們就有了結果！

值得記住的是，正則表達式非常強大。不幸的是（釋義），強大的力量帶來了巨大的複雜性。正則表達式很容易變得複雜且難以理解——但它們非常值得一讀——它們可以將程式碼頁變成專家手中的單行程式碼！

首先嘗試使用非正則表達式功能找到不同的解決方案總是值得的（參見解決方案 1），但它們有自己的位置，在這種情況下，它工作得相當好！上面連結的站點是開始探索它們的好地方。

  • 性能：10M 記錄的時間 = 14298.643 ms。
  • 與最快的比較 = 1.77 x

3：使用 REGEXP_REPLACE() 的替代正則表達式（不使用 REVERSE() -請參閱 Evan Carroll 的回答（個人小提琴））：

SELECT
 t_field,
 REGEXP_REPLACE(t_field, '(.*)\$', '\1-' )
FROM test
LIMIT 2;
  • 性能：10M 記錄的時間 = 16316.768 ms。
  • 與最快的比較 = 2.03 x

4：僅替代字元串函式，使用SUBSTRING(),POSITION()和LENGTH()（單獨的小提琴）：

SELECT
 t_field,
 REVERSE(
 SUBSTRING(REVERSE(t_field) FROM 1 FOR POSITION('$' IN REVERSE(t_field)) - 1)
 || '-' ||
 SUBSTRING(REVERSE(t_field) FROM POSITION('$' IN REVERSE(t_field)) + 1 FOR (LENGTH(REVERSE(t_field)))))
FROM test
LIMIT 2;
  • 性能：10M 記錄的時間 = 16316.768 ms。
  • 與最快的比較 = 2.34 x

5：（ARRAY手動） -v.慢但展示STRING_TO_ARRAY()，UNNEST()和1 （個人小提琴）WITH ORDINALITY

1：參見Erwin Brandstetter 的這些文章（1、2和3 ）WITH ORDINALITY

個人小提琴展示了許多方法以及性能分析和一些討論。僅出於完整性考慮而包含在內，在這種情況下，不作為一個現實的選擇。

儘管在這種特殊情況下，該ARRAY技術的性能不是很高（由於具有子查詢），但伺服器的許多後端程式碼都使用ARRAYs，它們通常是解決各種問題的最佳方法。了解 PostgreSQL 這個鮮為人知的角落是非常值得的。

首先要做的是：

SELECT
 UNNEST
 (STRING_TO_ARRAY(REVERSE((SELECT t.t_field 
                                   FROM test t
                                   WHERE t.id = 1
                                   )), '$'));

結果（OP的記錄-xxx由於REVERSE（），註釋首先出現）：

str
xxx
eod
nhoj
oof
rab

字元串按字元拆分為欄位$。

然後：

SELECT
 t.t_field,
 t.id, x.elem, x.num
FROM test t
LEFT JOIN LATERAL
 UNNEST(STRING_TO_ARRAY(REVERSE((SELECT t_field 
                                 FROM test
                                 WHERE test.id = t.id
                                 )), '$'))
 WITH ORDINALITY AS x (elem, num) ON TRUE
 LIMIT 5;

結果：

            t_field    id    elem  num
bar$foo$john$doe$xxx     1     xxx    1
bar$foo$john$doe$xxx     1     eod    2
bar$foo$john$doe$xxx     1    nhoj    3
bar$foo$john$doe$xxx     1     oof    4
bar$foo$john$doe$xxx     1     rab    5

我們需要 the 的原因WITH ORDINALITY是沒有 is，我們無法區分字元串的第一個元素（即我們感興趣的元素）和其他元素(elem, num)；

然後，我們這樣做：

SELECT
 (SELECT t_field FROM test WHERE test.id = tab.id),
 REVERSE(
 (STRING_TO_ARRAY((SELECT REVERSE(t_field) FROM test WHERE test.id = tab.id), '$'))[1]
 || '-' || 
 STRING_AGG(elem, '$'))
FROM
(
 SELECT
   t.id, x.elem, x.num
 FROM test t
 LEFT JOIN LATERAL
   UNNEST(STRING_TO_ARRAY(REVERSE((SELECT t_field 
                                   FROM test
                                   WHERE test.id = t.id
                                   )), '$'))
   WITH ORDINALITY AS x (elem, num) ON TRUE
) AS tab
WHERE tab.num &gt; 1
GROUP BY tab.id
LIMIT 2;

結果：

            t_field    result
bar$foo$john$doe$xxx    bar$foo$john$doe-xxx
7a29f$d06f$20e$21f$1b1  7a29f$d06f$20e$21f-1b1  -- will vary by fiddle run!
result
bar$foo$john$doe-xxx

這樣做是為了將反轉的字元串聚合回其原始形式，使用$作為分隔符，但不包括第一個元素 ( WHERE num &gt; 1;)。代替第一個元素的是第一個元素 - 數組引用[1]+ 連字元 ( || '-' ||) 等等，我們xxx-加上反向字元串的其他元素，並將$它們分開。

然後，我們只是簡單地應用於REVERSE()整個構造以給出所需的結果！

  • 性能：10M 記錄的時間 = 80715.198 ms。
  • 與最快的比較 = 10.04 x

有一個解決方案可能不使用WITH ORDINALITY（ROW_NUMBER()代替） - 請參閱個人小提琴中的討論。

表現

每個查詢都會顯示家庭 VM 上 1000 萬條記錄的性能數據 - db<>fiddle（30,000 條記錄）結果在相對量級方面相當接近地反映了它們。

因此，在這種情況下，盡可能使用基於字元串的方法，但正則表達式可以幫助減少SLOC計數，但是它們可能會更慢 - 由 DBA/Dev 在速度和復雜性之間做出選擇。

引用自：https://dba.stackexchange.com/questions/303245

相關問答

Postgresql

返回具有可變長度的字元串的一部分

  • March 23, 2022
檢視問答
Postgresql

將 regexp_split_to_array() 的結果分配給 Pl/pgSQL 數組變數

  • January 7, 2022
檢視問答
Postgresql

如何在 postgres 中僅儲存美國/加拿大電話號碼？

  • December 22, 2021
檢視問答
Postgresql

使用正則表達式的約束檢查值

  • October 16, 2021
檢視問答
Postgresql

在 PostgreSQL 中使用 LIKE、SIMILAR TO 或正則表達式進行模式匹配

  • July 14, 2021
檢視問答
Postgresql

如何刪除特殊字元，但替換空格？

  • May 18, 2021
檢視問答