如何在Snowflake中为列值中间添加随机字符串以更新列值

需求说明 #

针对store表中recorded日期在2022-01-01至2022-04-30之间的记录，将Name字段的值在倒数第一个字符前插入随机字母数字字符串，例如ABC变为ABxxxxC，PQS变为PQxxxxS。

问题分析 #

你提供的原语句存在几个问题：

1. INSERT函数参数错误：硬编码插入位置为3、删除2个字符，仅适用于固定3位的字符串，且会因原字符串长度不足导致结果不符合预期；
2. 不必要的CTE：如果不需要相同Name值更新后生成一致的随机串，CTE完全可以省略；
3. 未适配不同长度的Name字段，灵活性不足。

解决方案 #

根据是否需要相同Name值生成一致的随机串，提供两种方案：

方案1：每条记录生成独立随机串（无需同值一致）

直接在UPDATE语句中处理，每条符合条件的记录生成独立的随机串：

UPDATE store
SET Name = INSERT(Name, LENGTH(Name), 0, RANDSTR(5, RANDOM()))
WHERE recorded::DATE BETWEEN '2022-01-01'::DATE AND '2022-04-30'::DATE;

• 关键函数说明：
  • RANDSTR(5, RANDOM())：生成5位随机字母数字字符串，可修改第一个参数调整随机串长度；
  • INSERT(Name, LENGTH(Name), 0, ...)：在Name字符串的最后一个字符位置前插入随机串（LENGTH(Name)适配任意长度的字符串），0表示不删除原有字符；
  • LENGTH(Name)：动态获取字符串长度，确保插入位置准确。

方案2：相同Name值生成一致随机串

如果需要同一Name值更新后保持相同的随机串（例如所有ABC都变为同一个ABxxxxC），可以用CTE预先生成每个Name对应的固定随机串，再关联更新：

WITH name_random_mapping AS (
    SELECT DISTINCT Name,
           INSERT(Name, LENGTH(Name), 0, RANDSTR(5, HASH(Name))) AS new_name
    FROM store
    WHERE recorded::DATE BETWEEN '2022-01-01'::DATE AND '2022-04-30'::DATE
)
UPDATE store t1
SET t1.Name = nr.new_name
FROM name_random_mapping nr
WHERE t1.Name = nr.Name
AND t1.recorded::DATE BETWEEN '2022-01-01'::DATE AND '2022-04-30'::DATE;

• 核心调整：用HASH(Name)作为RANDSTR的种子，确保同一Name生成固定的随机串。

示例验证 #

输入表（store）

执行方案1后的输出（随机串每次执行不同）

执行方案2后的输出（同一Name的随机串固定）

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