SQL Server : how does the Replace function work as

Question

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

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')

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

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

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

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'),' ','');

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?

Answer 1

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?

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.

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.)

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.

Answer 2

Your results are not surprising, since each replace is returning the string to the next level. There is no way to distinguish between the original character value and the replaced value, when they are the same character.

If you were only working with alpha characters you could do the following.

1. Change the collation of the original string.
2. Upper case the string
3. Replace the upper case letters with the lower case
4. Lower case the entire string at the end (should be redundant)

Unfortunately, I can't think of an analog for numbers that would work the same way.

Here is a link to a site that has code for the function http://www.dbforums.com/microsoft-sql-server/1216565-oracle-translate-function-equivalent-sql-server.html. The equivalent function in Oracle is called translate.

Answer 3

The Replace() function simply performs the operation and returns. It doesn't keep state to the next Replace(). It doesn't prevent a later invocation from replacing the characters you previously replaced. To cycle characters around, you have to have an additional "placeholder" value, and then take care to not replace a character that was already change from something else.

First, let me show you an analogy:

There are three buckets full of an equal number of marbles. The buckets are labeled "A", "B", and "C". You must perform the following instructions:

• Pour all marbles from A into C.
• Pour all marbles from B into A.
• Pour all marbles from C into B.

What result would you expect to have? The answer is: an empty bucket C, and B having twice the number of marbles as are in A.

If you want to preserve the marbles in their original counts, you have to have four containers so that you don't mix the marbles while moving them:

• Pour all marbles from C into X.
• Pour all marbles from A into C.
• Pour all marbles from B into A.
• Pour all marbles from X into B. (rather than from C--which already has A's marbles)

Now you have the results you expected, and can discard the empty bucket X.

Try this expression and see if it gives what you want:

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