SQL Server:替换功能如何工作，因为它对我来说很奇怪 [英] SQL Server : how does the Replace function work as its acting strange for me

问题描述

我正在 SQL Server 表中随机化"一些字符串以对它们进行原始加密.

I am 'randomising' some strings in a SQL Server table to do a primitive encryption on them.

我有一个嵌套的 SQL 替换函数大约 35 次 (A-Z,1-9)，它基本上采用字母表和数字中的每个字母并将其替换为另一个字母或数字.其中的例子是

I have a nested SQL replace function around 35 times (A-Z,1-9) that basically takes every letter in the alphabet and number and replaces it with another letter or number. example of which would be

Replace(Replace(Replace('a', 'c'), 'b', 'a'), 'c', 'b')

我认为替换函数会遍历像 'abc' 这样的字符串，然后替换所有内容并停止 - 'cab'.没有！

I figured that the replace function would go though a string like 'abc' and replace everything once and stop - 'cab'. It doesn't!

它似乎想再次更改某些字符导致 'abc'->'cab'->'ccb'.

It seems to want to change some characters again resulting in 'abc'->'cab'->'ccb'.

这很好，除非我有另一个名为 'aac' 的字符串，这可能会导致重复的字符串并且我失去了对原始字符串的可追溯性.

This is fine except if I have another string called 'aac' this could result in duplicate string and I lose traceability back to original.

谁能解释我如何阻止 REPLACE() 部分返回我的字符串?

Can anyone explain how I could stop REPLACE() partially going back over my string?

SELECT * INTO example_temp FROM example;
Update KAP_db.dbo.example_temp Set col1 = replace(replace(replace(replace(replace(
replace(replace(replace(replace(replace(‌​replace(replace(replace(replace(replace(
replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
replace‌​(replace(replace(replace(replace(replace(replace(replace(replace(replace(
col1, 'A', 'N'),'B', 'O'), 'C', 'P'), 'D', 'Q'), 'E', 'R'), 'F', 'S'), 'G', 'T'),
'H', 'U'), 'I', 'V'), 'J', 'W'), 'K', 'X'), 'L', 'Y'), 'M', 'Z'), 'O', 'A'), 'P', 'B'),
'Q', 'C'), 'R', 'D'),'S', 'E'),'T', 'E'),'U', 'E'),'V', 'F'),'W', 'G'),'X', 'H'),
'Y', 'I'),'Z', 'J'), '1', '9'),'2','8'),'3','7'),'4','6'),'5','5'),'6','4'),'7','3'),
'8','2'),'9','1'),' ','');

上面的结果是'8EVHUAB'和'8EVHHAB'都输出'2DFEENA'

The above results in '8EVHUAB' and '8EVHHAB' both outputting '2DFEENA'

更新-------------------------------------------------------------------

Update -------------------------------------------------------------------

好的，我已经重做了代码，到目前为止:

Ok i have redone the code and so far have:

 DECLARE @Input AS VarChar(1000)
 DECLARE @i AS TinyInt
 Declare @Substring AS VarChar(1000)
 Declare @Prestring  AS VarChar(1000)
 Declare @Poststring  AS VarChar(1000)

 Select @Input='ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789'

 SELECT @i = 1
 Select @Substring ='na'
 WHILE @i <= LEN(@Input) BEGIN

Select @Prestring = SUBSTRING(@Input,-1,@i)
Select @Poststring = SUBSTRING(@Input,@i+1,LEN(@Input))




 SELECT @Substring =  replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace
(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace
(SUBSTRING(@Input,@i,1), 'A', 'N'),'B', 'O'), 'C', 'P'), 'D', 'Q'), 'E', 'R'), 'F', 'S'), 'G', 'T'), 'H', 'U'), 'I', 'V'), 'J', 'W'), 'K', 'X'), 'L', 'Y'), 'M', 'Z'), 'N', 'A'), '0', 'B'), 'P', 'C')
, 'Q', 'D'),'R', 'E'),'S', 'E'),'T', 'E'),'U', 'F'),'V', 'G'),'W', 'H'),'X', 'I'),'Y', 'J'), '1', '9'),'2','8'),'3','7'),'4','6'),'5','5'),'6','4'),'7','3'),'8','2'),'9','1'),' ','')

Select @Input = @Prestring + @Substring + @Poststring

 SELECT @i = @i + 1


 print 'END
 '

 END

但是这不能正常工作，代码没有按照其编写的方式执行，有什么建议吗?

This doesnt work correctly though, the code does not execute as its written, any suggestions?

推荐答案

为什么你会看到这个: replace 是一个函数；它所知道的只是它的论据.replace(replace('aba', 'a', 'b'), 'b', 'a') 绝对等价于 replace('bbb', 'b', 'a')，因为外部 replace 无法知道它的第一个参数是由对 replace 的不同调用创建的.有意义吗?

Why you're seeing this: replace is a function; all it knows are its arguments. replace(replace('aba', 'a', 'b'), 'b', 'a') is absolutely equivalent to replace('bbb', 'b', 'a'), because the outer replace has no way of knowing that its first argument was created by a different call to replace. Does that make sense?

你可以把它想象成代数中的一个函数.如果我们定义 f(x) = x²，那么 f(f(2)) = f(2²) = f(4) = 4² = 16. 没有办法告诉 f 当它的参数是 f(2) 和它的参数是 4 时的行为不同，因为 f(2) 是 4.

You can think of it just like a function in algebra. If we define f(x) = x², then f(f(2)) = f(2²) = f(4) = 4² = 16. There's no way to tell f to behave differently when its argument is f(2) from when its argument is 4, because f(2) is 4.

同理，replace('aba', 'a', 'b') is 'bbb'，所以没有办法告诉 replace 在它的第一个参数是 replace('aba', 'a', 'b') 和它的第一个参数是 'bbb' 时表现不同.

Similarly, replace('aba', 'a', 'b') is 'bbb', so there's no way to tell replace to behave differently when its first argument is replace('aba', 'a', 'b') from when its first argument is 'bbb'.

(这在计算机科学中通常是正确的.计算机科学中的函数并不总是像代数中的函数—例如，它们实际上经常做事情，而不仅仅是返回一个值—但通常情况下，它们将参数作为值或对值的不透明引用接收，并且无法知道它们来自何处或如何构造.)

(This is usually true in computer science. Functions in computer science aren't always like functions in algebra — for example, they frequently actually do things, rather than just returning a value — but it's usually the case that they receive arguments as values, or as opaque references to values, and have no way of knowing where they came from or how they were constructed.)

如何解决这个问题:我认为没有任何非常干净的方法可以做到这一点.Gordon Linoff 建议您可以使用初始字符串中不存在且最终字符串中不存在的中间占位符字符(特别是 — 小写字母)，以便您可以安全地替换它们无需担心干扰；我认为这可能是最好的方法.

How to address this: I don't think there's any very clean way to do this. Gordon Linoff suggested that you could use intermediate placeholder characters (specifically — lowercase letters) that don't exist in the initial string and don't exist in the final string, so that you can safely replace them without worrying about interference; and I think that's probably the best approach.

这篇关于SQL Server:替换功能如何工作，因为它对我来说很奇怪的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！