如何修剪transactions表中Customer_Transactions_ID列的AB_EL格式值后缀？

当然可以实现！这个需求本质上是对以AB_EL开头的字符串做结构化截取，只保留前面固定的四个分隔段（即AB_EL_XXX_XXXX格式），不管后面跟着多少个后缀都统一去除。下面针对不同主流数据库给出具体的实现方案：

MySQL/MariaDB #

MySQL和MariaDB有个非常方便的函数SUBSTRING_INDEX，可以直接按分隔符截取指定次数的内容，完美适配你的需求：

-- 先验证结果是否符合预期（强烈建议先执行这个）
SELECT 
    Customer_Transactions_ID AS original_id,
    SUBSTRING_INDEX(Customer_Transactions_ID, '_', 4) AS trimmed_id
FROM transactions
WHERE Customer_Transactions_ID LIKE 'AB_EL%';

-- 确认无误后执行更新
UPDATE transactions
SET Customer_Transactions_ID = SUBSTRING_INDEX(Customer_Transactions_ID, '_', 4)
WHERE Customer_Transactions_ID LIKE 'AB_EL%';

解释：SUBSTRING_INDEX(column, '_', 4)会从左到右截取到第4个下划线分隔的部分，自动截断后面所有的后缀内容——不管原字符串是AB_EL_205_72330_H还是AB_EL_820_23066_E_N，都能得到你想要的AB_EL_205_72330或AB_EL_820_23066。

SQL Server #

SQL Server没有直接的SUBSTRING_INDEX，但可以通过字符串拆分和聚合来实现。如果你的SQL Server版本是2022及以上，支持STRING_SPLIT的ordinal参数，写法会很简洁：

-- 先验证
SELECT 
    t.Customer_Transactions_ID AS original_id,
    agg.new_id AS trimmed_id
FROM transactions t
CROSS APPLY (
    SELECT STRING_AGG(value, '_') AS new_id
    FROM STRING_SPLIT(t.Customer_Transactions_ID, '_')
    WHERE ordinal <= 4
) agg
WHERE t.Customer_Transactions_ID LIKE 'AB_EL%';

-- 执行更新
UPDATE t
SET Customer_Transactions_ID = agg.new_id
FROM transactions t
CROSS APPLY (
    SELECT STRING_AGG(value, '_') AS new_id
    FROM STRING_SPLIT(t.Customer_Transactions_ID, '_')
    WHERE ordinal <= 4
) agg
WHERE t.Customer_Transactions_ID LIKE 'AB_EL%';

如果是2016-2019版本（不支持ordinal），可以用CTE生成序号来实现：

-- 先验证
WITH SplitCTE AS (
    SELECT 
        Customer_Transactions_ID,
        value,
        ROW_NUMBER() OVER (PARTITION BY Customer_Transactions_ID ORDER BY (SELECT NULL)) AS rn
    FROM transactions
    CROSS APPLY STRING_SPLIT(Customer_Transactions_ID, '_')
    WHERE Customer_Transactions_ID LIKE 'AB_EL%'
)
SELECT 
    Customer_Transactions_ID AS original_id,
    (SELECT STRING_AGG(value, '_') FROM SplitCTE c WHERE c.Customer_Transactions_ID = s.Customer_Transactions_ID AND c.rn <=4) AS trimmed_id
FROM SplitCTE s
GROUP BY Customer_Transactions_ID;

-- 执行更新
WITH SplitCTE AS (
    SELECT 
        Customer_Transactions_ID,
        value,
        ROW_NUMBER() OVER (PARTITION BY Customer_Transactions_ID ORDER BY (SELECT NULL)) AS rn
    FROM transactions
    CROSS APPLY STRING_SPLIT(Customer_Transactions_ID, '_')
    WHERE Customer_Transactions_ID LIKE 'AB_EL%'
)
UPDATE t
SET Customer_Transactions_ID = (
    SELECT STRING_AGG(value, '_') 
    FROM SplitCTE c
    WHERE c.Customer_Transactions_ID = t.Customer_Transactions_ID AND c.rn <=4
)
FROM transactions t
WHERE t.Customer_Transactions_ID LIKE 'AB_EL%';

PostgreSQL #

PostgreSQL可以通过数组转换来实现，写法非常直观：

-- 先验证
SELECT 
    Customer_Transactions_ID AS original_id,
    ARRAY_TO_STRING((STRING_TO_ARRAY(Customer_Transactions_ID, '_'))[1:4], '_') AS trimmed_id
FROM transactions
WHERE Customer_Transactions_ID LIKE 'AB_EL%';

-- 执行更新
UPDATE transactions
SET Customer_Transactions_ID = ARRAY_TO_STRING((STRING_TO_ARRAY(Customer_Transactions_ID, '_'))[1:4], '_')
WHERE Customer_Transactions_ID LIKE 'AB_EL%';

解释：STRING_TO_ARRAY把字符串按下划线拆分成数组，[1:4]取数组的前4个元素（PostgreSQL数组下标从1开始），再用ARRAY_TO_STRING拼接回字符串。

重要提醒 #

不管使用哪种数据库，一定要先执行SELECT验证结果，确认修剪后的内容完全符合预期后，再执行UPDATE语句，避免误修改数据。

内容的提问来源于stack exchange，提问作者Taz