Alternative Rot13 Implementation.

Last issue was about ufn_Rot13 a letter exchange cipher contributed by Luke Schollmeyer. The Rot13 cipher is often used for very basic hiding of text. The Windows Registry uses it and it's also used in may web cookies.

ufn_Rot13 uses a loop to compute each letter in the output. This technique isn't that unusual but it uses a relatively large amount of SQL Server's computational capability. At least compared to the requirements of performing data manipulation alone.

After Luke sent the original ufn_Rot13 script, he sent a revised version that doesn't use an explicit loop. Instead it uses a table and gets the SQL engine to perform the loop in a select statement. This provides a good contrast to the plain loop and demonstrates one way of speeding up UDFs.

Let's start with the table creation script:

create table numbers (
 number int
) 
GO

declare @i int
 
set @i = 0
while @i <= 8000 begin
 insert numbers values(@i)
 set @i = @i + 1 
end
GO 

Now we can create the revised UDF. Here's the CREATE FUNCTION script:

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create function ufn_Rot13Redux (@input varchar(8000))
 returns varchar(8000)
as 
begin
 declare @pass varchar(8000)
 
 set @pass = ''
 select 
  @pass = @pass + 
      CASE 
      WHEN ASCII(SUBSTRING(@input,n.number+1,1)) BETWEEN 97 AND 122 
       THEN CASE WHEN ASCII(SUBSTRING(@input,n.number+1,1)) - 13 < 97 
                 THEN char((ASCII(SUBSTRING(@input,n.number+1,1)) - 13) 
                                              + 122 - 96)
         ELSE char(ASCII(SUBSTRING(@input,n.number+1,1)) - 13) END
      WHEN ASCII(SUBSTRING(@input,n.number+1,1)) BETWEEN 65 AND 90 
       THEN CASE WHEN ASCII(SUBSTRING(@input,n.number+1,1)) - 13 < 65 
                 THEN char((ASCII(SUBSTRING(@input,n.number+1,1)) - 13) 
                                                    + 90 - 64)
         ELSE char(ASCII(SUBSTRING(@input,n.number+1,1)) - 13) END
      ELSE CHAR(ASCII(SUBSTRING(@input,n.number+1,1)))
      END 
 from
  numbers n 
 where
  n.number < len(@input)
 
 return @pass 
end
	
go

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT EXEC ON dbo.ufn_Rot13Redux to PUBLIC
GO

Let's use the same simple phrase as last time for a basic test. The script illustrates a test of translating a phrase from plain text to encrypted text and back to a decrypted message:

DECLARE @PlainText varchar(8000)  -- Original Message
      , @CryptoText varchar(8000) -- Encrypted text
      , @Decrypted varchar(8000)  -- After decryption

SELECT @PlainText = 'We are ever true to Brown'
SELECT @CryptoText = dbo.ufn_rot13redux(@PlainText)
SELECT @Decrypted = dbo.ufn_rot13redux(@Cryptotext)

SELECT CASE WHEN @PlainText = @Decrypted 
            THEN 'Equal' ELSE 'Not equal' END [Test]
      , @CryptoText [Encrypted]
      , @Decrypted [Decrypted]

go


(Results)
Test  Encrypted                  Decrypted                
----- -------------------------- -------------------------
Equal Jr ner rire gehr gb Oebja  We are ever true to Brown

Luke used sort of a SQL trick in the SELECT statement:

 SELECT @pass = @pass +  CASE  .....

Since the SELECT statement is executed for every row it gets executed for every character in the input string and the result is concatenated to @Pass. Of course that means that there are as many concatenation operations as there are letters in the input but there were just as many in the original version with the WHILE loop. Of course, this time they're all being executed in the SELECT statement.

Due to the need for a lot of setup for the test, I'm going to postpone a performance comparison between the type versions of the ROT13 algorithm for next issue.

Do you have a great UDF that you'd like to share?  Or maybe you have a T-SQL problem that you think could be solved by a UDF but you don't know how? Send them to: UDF@NovickSoftware.com

and they might be published in this newsletter.  I try and respond to every request that I get. 

Thanks, 
Andrew Novick