如何用EncryptByKey替换varchar(max)列部分文本为加密值并解密对应部分？

可以实现，但你的写法存在类型不匹配问题：EncryptByKey返回的是varbinary类型，直接与varchar字符串拼接会触发隐式类型转换，导致结果异常。以下是正确的实现方式：

一、正确加密部分文本 #

需要将加密后的二进制数据转换为可存储的字符串格式（推荐Base64），保证替换后整个字段仍为合法的varchar(max)类型。同时建议给加密内容添加唯一标记，方便后续定位解密。

示例SQL：

-- 先打开对称密钥（必须执行）
OPEN SYMMETRIC KEY encKey
DECRYPTION BY ...; -- 替换为你的密钥解密方式，比如证书、密码等

-- 更新字段，替换指定文本为加密后的Base64字符串（带标记）
UPDATE your_table
SET data = REPLACE(
    data,
    matched_string,
    '[ENC]' + CONVERT(varchar(max), EncryptByKey(Key_GUID('encKey'), matched_string), 1) + '[/ENC]'
)
WHERE CHARINDEX(matched_string, data) > 0; -- 仅更新包含目标文本的记录

-- 关闭密钥
CLOSE SYMMETRIC KEY encKey;

说明：CONVERT的style=1会将二进制数据转为带连字符的Base64格式，style=2则不带连字符，解密时需对应使用相同的style。

二、解密指定部分文本 #

通过之前添加的标记定位加密内容，将Base64字符串转回二进制后解密：

示例SQL：

-- 打开密钥
OPEN SYMMETRIC KEY encKey
DECRYPTION BY ...;

-- 提取并解密指定部分
SELECT
    data AS original_data,
    -- 提取标记包裹的加密内容并解密
    DecryptByKey(CONVERT(varbinary(max), 
        SUBSTRING(data, CHARINDEX('[ENC]', data) + 5, 
        CHARINDEX('[/ENC]', data) - CHARINDEX('[ENC]', data) - 5), 1)) AS decrypted_content
FROM your_table
WHERE CHARINDEX('[ENC]', data) > 0;

-- 关闭密钥
CLOSE SYMMETRIC KEY encKey;

注意事项 #

• 必须确保在加密/解密前打开对称密钥，操作完成后关闭密钥；
• 标记[ENC]和[/ENC]可自定义，避免与原文本内容冲突；
• 如果原字段包含大量数据，建议添加WHERE条件缩小操作范围，提升性能。

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