SSMS字符串混淆

Question

我需要创建一个脚本来混淆一些数据。这个字符串很长，我只需要混淆它的一部分。

在该表中，记录如下所示：

<?xml version="1.0" encoding="UTF-8"?><CONTRACT><IBC IBC_REF="f45f1231234ae5ac2easdasdfde5dfd" IBC_TYPE="I" TELEPHONE_1="1111111" TELEPHONE_2="11111111" MOBILE_PHONE="11111111" E_MAIL="asdasdasd@hotmail.com" SOLICITATION_MAIL="0" ARREARS_MAIL="1" MAIL_REDIRECTED="0" TITLE="Mrs" SURNAME_REGISTERED_NAME="Assadasd"

它需要变成这样：

<?xml version="1.0" encoding="UTF-8"?><CONTRACT><IBC IBC_REF="f45f1231234ae5ac2easdasdfde5dfd" IBC_TYPE="I" TELEPHONE_1="Telephone-1" TELEPHONE_2="Telephone-2" MOBILE_PHONE="MobilePhone" E_MAIL="email-1" SOLICITATION_MAIL="0" ARREARS_MAIL="1" MAIL_REDIRECTED="0" TITLE="Mrs" SURNAME_REGISTERED_NAME="Surname" 

如何更新表中的所有行，并通过保存其他字符串来仅更改部分字符串？

Answer 1

看起来您是在处理XML字符串，所以我建议使用SQL Server XML功能。下面是一个简短的示例：

DECLARE @input NVARCHAR(4000) = '<?xml version="1.0" encoding="UTF-8"?><CONTRACT><IBC IBC_REF="f45f1231234ae5ac2easdasdfde5dfd" IBC_TYPE="I" TELEPHONE_1="1111111" TELEPHONE_2="11111111" MOBILE_PHONE="11111111" E_MAIL="asdasdasd@hotmail.com" SOLICITATION_MAIL="0" ARREARS_MAIL="1" MAIL_REDIRECTED="0" TITLE="Mrs" SURNAME_REGISTERED_NAME="Assadasd" /></CONTRACT>';
DECLARE @x xml = CONVERT(xml, REPLACE(@input,'encoding="UTF-8"','encoding="UTF-16"'));

SELECT @x.value('(/CONTRACT/IBC/@TELEPHONE_1)[1]', 'nvarchar(100)');

DECLARE @y xml = @x

SET @y.modify('replace value of (/CONTRACT/IBC/@TELEPHONE_1)[1] with "TELEPHONE_1"');
SELECT @y.value('(/CONTRACT/IBC/@TELEPHONE_1)[1]', 'nvarchar(100)');
SELECT @y

DECLARE @output NVARCHAR(4000) = '<?xml version="1.0" encoding="UTF-8"?>' + CONVERT(NVARCHAR(4000), @y)
SELECT @output