PostgreSQL 查詢替換函式

兆鑫軟體發表於2021-12-21
SQL函式

概要: 在本教程中，我們將向您介紹 PostgreSQL 替換函式，這些函式在字串中搜尋子字串並將其替換為新的子字串。

PostgreSQL REPLACE 函式

有時，您想搜尋列中的字串並將其替換為新字串，例如替換過時的電話號碼、損壞的 URL 和拼寫錯誤。

要搜尋字串的所有匹配項並將其替換為新字串，請使用 REPLACE() 函式。

下面說明了 PostgreSQL REPLACE() 函式的語法：

REPLACE(source, old_text, new_text );

REPLACE() 函式接受三個引數：

  • source 是一個你想要替換的字串。
  • old_text 是您要搜尋和替換的文字。 如果 old_text 在字串中多次出現，它的所有出現都將被替換。
  • new_text 是將替換舊文字 (old_text) 的新文字。

PostgreSQL REPLACE() 函式示例

請參閱以下使用“REPLACE()”函式的示例：

SELECT
    REPLACE ('ABC AA', 'A', 'Z');

PostgreSQL REPLACE 示例

在此示例中，我們將字串中的所有字元“A”替換為字元“Z”。

以下示例將 URL 中的子字串“tt”替換為“xx”：

SELECT
    REPLACE (
        'https://www.zhaoxinsoft.com',
        'tt',
        'xx'
    );

PostgreSQL-REPLACE-example-2

如果要搜尋和替換表列中的子字串，請使用以下語法：

UPDATE 
   table_name
SET 
   column_name = REPLACE(column,old_text,new_text)
WHERE 
   condition

讓我們使用示例資料庫中的customer表進行演示：

SELECT
    first_name,
    last_name,
    email
FROM
    customer;

customer table

現在，假設您想更新電子郵件列以用 zhaoxinsoft.com 替換域 sakilacustomer.org，請使用以下語句：

UPDATE 
   customer
SET 
   email = REPLACE (
      email,
    'sakilacustomer.org',
    'zhaoxinsoft.com'
   );

因為我們省略了 WHERE 子句，所以更新了 customer 表中的所有行。

讓我們驗證是否發生了替換。

SELECT
    first_name,
    last_name,
    email
FROM
    customer;

postgresql-replace-in-database

PostgreSQL REGEXP_REPLACE 函式

如果您需要更高階的匹配方式，您可以使用REGEXP_REPLACE() 函式。

REGEXP_REPLACE() 函式允許您替換與正規表示式匹配的子字串。 下面說明了REGEX_REPLACE() 函式的語法。

REGEXP_REPLACE(source, pattern, new_text [,flags])

REGEXP_REPLACE() 函式接受四個引數：

  • source 是您將查詢與 pattern 匹配的子字串並將其替換為 new_text 的字串。 如果未找到匹配項，則源保持不變。
  • pattern 是一個正規表示式。 它可以是任何模式，例如：電子郵件、URL、電話號碼等。
  • next_text 是替換子字串的文字。
  • flags 包含零個或多個單字母標誌，用於控制 REGEX_REPLACE() 函式的行為。 例如，i 表示不區分大小寫匹配或忽略大小寫。 g 代表全域性； 如果使用 g 標誌，該函式將替換所有匹配模式的子字串出現。 flags 引數是可選的。

PostgreSQL REGEXP_REPLACE() 函式示例

需要努力和實驗才能理解REGEXP_REPLACE() 函式的工作原理。

以下是使用“REGEXP_REPLACE()”函式的示例。

SELECT
    regexp_replace(
        'foo bar foobar barfoo',
        'foo',
        'bar'
    );
bar bar foobar barfoo

在下面的例子中，因為我們使用了 i 標誌，它會忽略大小寫並用 foo 替換第一次出現的 Bar 或 bar。

SELECT
    regexp_replace(
        'Bar foobar bar bars',
        'Bar',
        'foo',
        'i'
    );
foo foobar bar bars

在下面的例子中，我們使用了 g 標誌，所有出現的 bar 都被替換為 foo。 請注意，Bar、BAR 或bAR 不會改變。

SELECT
    regexp_replace(
        'Bar sheepbar bar bars barsheep',
        'bar',
        'foo',
        'g'
    );
Bar sheepfoo foo foos foosheep

在下面的示例中，我們同時使用了 g 和 i 標誌，因此所有出現的 bar 或 Bar、BAR 等都被替換為 foo。

SELECT
    regexp_replace(
        'Bar sheepbar bar bars barsheep',
        'bar',
        'foo',
        'gi'
    );
foo sheepfoo foo foos foosheep

\m 表示只匹配每個單詞的開頭。 在任何情況下，所有以 bar 開頭的單詞都被 foo 替換。 以 bar 結尾的單詞沒有改變。

SELECT
    regexp_replace(
        'Bar sheepbar bar bars barsheep',
        '\mbar',
        'foo',
        'gi'
    );
foo sheepbar foo foos foosheep

\M 表示僅在每個單詞的末尾匹配。 所有以“bar”結尾的單詞都被 foo 代替。 以bar開頭的詞不會被替換。

SELECT
    regexp_replace(
        'Bar sheepbar bar bars barsheep',
        'bar\M',
        'foo',
        'gi'
    );
foo sheepfoo foo bars barsheep

\m 和 \M 表示在每個單詞的開頭和結尾都匹配。 在任何情況下，所有以 bar 開頭和結尾的單詞都被替換為 foo。

SELECT
    regexp_replace(
        'Bar sheepbar bar bars barsheep',
        '\mbar\M',
        'foo',
        'gi'
    );
foo sheepbar foo bars barsheep

PostgreSQL TRANSLATE 函式

除了REPLACE() 和REGEXP_REPLACE() 函式，PostgreSQL 還提供了另一個名為TRANSLATE() 的函式用於字串替換。

給定一個set字元，TRANSLATE()函式用new_set中的字元替換source字串中匹配set的任何字元。

TRANSLATE(source, set, new_set);

The TRANSLATE() function accepts three parameters:

  • source 是您要搜尋和替換的字串。
  • set 是一組用於匹配的字元。
  • new_set 是一組字元，用於替換與 set 匹配的字元。

請注意，如果 set 的字元比 new_set 多，PostgreSQL 會從 source 字串中刪除 set 中多餘的字元。

PostgreSQL TRANSLATE 函式示例

在以下示例中，我們將所有特殊母音轉換為正常母音。

SELECT
    TRANSLATE (
        'LÒ BÓ VÔ XÕ',
        'ÒÓÔÕ',
        'OOOO'
    );
LO BO VO XO

請看下圖。

postgresql translate function

在本教程中，我們向您展示了各種函式：REPLACE()、REGEXP_REPLACE() 和TRANSLATE() 用於搜尋子字串並將其替換為新的子字串。

相關文章