Sometimes you might want to scramble some text, for example if you want a randomized string for a password or key of some sort. What real world application could such a function be used?  In cryptography, for example if you need to scramble selected letters, numbers and symbols into a stronger password. Or in some word puzzles, one might be challenged to find the real word. Whatever the need, for applications that prefer to conduct the scrambling of text from within a database one approach is to create a temporary table to hold the values of single letters in each row and create a randomly generated value as a key to sort by. When sorted on the random number, the letters are then scrambled in a hard to predict pattern.

It would be useful to be able to run this:

SELECT [dbo].[ScrambleText](‘This_String_is_100%_Organized!’)

With the result being something like this:

adires_nnh%0t!rii1__gS0_OzsgiT

The code to create such a function is listed below:

Create function [dbo].[ScrambleText](@TextToScramble varchar(30)) RETURNS varchar(52)

as

BEGIN

DECLARE @counter smallint,
@TextToScrambleLen tinyint,
@pos int,@retVal varchar(52),
@maxRandID float,
@myChar varchar(1),
@Scrambled Varchar(30),
@rowCount tinyint,
@test tinyint

DECLARE @ScrambleTable TABLE (letter varchar(1),randid float)

set @pos =0

set @TextToScrambleLen =len(@TextToScramble)

set @Scrambled='';

SET @counter = 0

WHILE @counter < @TextToScrambleLen
begin 
 SET @counter = @counter + 1
 insert into @ScrambleTable (letter,randid) values (substring(@TextToScramble,@counter,1), (select RandNum from Rand_v))
end
set @rowCount = 0
set @counter=0
set @maxRandID = 2
select @rowCount = count(*) from @ScrambleTable
set @test=0
set @test=@test+1

WHILE @counter < @rowCount
begin
 select @maxRandID = max(randid) from @ScrambleTable where randid < @maxRandID
 select @myChar = letter from @ScrambleTable where randid = @maxRandID
 set @Scrambled = @Scrambled + @myChar
 select @counter = @counter + 1
end

return @Scrambled

END