ROT47 function implementation

Question

According to Wikipedia, here below is the definition of the algorithm:

ROT13 ("rotate by 13 places", sometimes hyphenated ROT-13) is a simple letter substitution cipher that replaces a letter with the letter 13 letters after it in the alphabet. ROT13 is a special case of the Caesar cipher, developed in ancient Rome.

ROT47 is a derivative of ROT13 which, in addition to scrambling the basic letters, also treats numbers and common symbols. Instead of using the sequence A–Z as the alphabet, ROT47 uses a larger set of characters from the common character encoding known as ASCII. Specifically, the 7-bit printable characters, excluding space, from decimal 33 '!' through 126 '~', 94 in total, taken in the order of the numerical values of their ASCII codes, are rotated by 47 positions, without special consideration of case. For example, the character A is mapped to p, while a is mapped to 2.

I already implemented it in C++ but this time, I have implemented it in SQL Server. Here below is the user-defined function I wrote:

CREATE FUNCTION [dbo].[ROT47]
( 
  @PLAIN_TEXT nvarchar(MAX)
)
RETURNS nvarchar(MAX)
AS
BEGIN
  DECLARE @ENCRYPTED_TEXT nvarchar(MAX) = N''
  DECLARE @LENGTH_TEXT int = 0
  DECLARE @c nvarchar = N''
  DECLARE @i int = 1

  SET @LENGTH_TEXT = LEN(@PLAIN_TEXT)

  WHILE (@i <= @LENGTH_TEXT)
  BEGIN
    SET @c = SUBSTRING(@PLAIN_TEXT, @i, 1)

    IF (ASCII(@c) BETWEEN ASCII(N'!') AND ASCII(N'~'))
    BEGIN
      SET @c = char(ASCII(N'!') + (ASCII(@c) - ASCII(N'!') + 47) % 94)
      SET @ENCRYPTED_TEXT = @ENCRYPTED_TEXT + @c
    END

    SET @i = @i + 1
  END

  RETURN @ENCRYPTED_TEXT
END

Below is the query I wrote to test my UDF:

DECLARE @PLAIN_TEXT nvarchar(MAX) = N'HelloWorld'
DECLARE @ENCRYPTED_TEXT nvarchar(MAX)
DECLARE @DECRYPTED_TEXT nvarchar(MAX)

SET @ENCRYPTED_TEXT = ( SELECT dbo.ROT47(@PLAIN_TEXT) )
SET @DECRYPTED_TEXT = ( SELECT dbo.ROT47(@ENCRYPTED_TEXT) ) 

SELECT @PLAIN_TEXT AS PLAIN_TEXT, 
       @ENCRYPTED_TEXT AS ENCRYPTED_TEXT, 
       @DECRYPTED_TEXT AS DECRYPTED_TEXT

As expected, the above query gives the following result:

+--------------+------------------+------------------+
|  PLAIN_TEXT  |  ENCRYPTED_TEXT  |  DECRYPTED_TEXT  |
+--------------+------------------+------------------+
|  HelloWorld  |    w6==@(@C=5    |    HelloWorld    |
+--------------+------------------+------------------+

What do you think about my implementation ? Is there a way to improve it ? I know loops are things you're trying to avoid in SQL Server but is there a way to avoid using a loop in my case ?

Answer 1

Most functions are ugly in SQL Server. When there is something that needs to be done that is ugly, it's what they are used for IMHO. With that being said, here is a non-looping way. You'd have to run it in your environment to see which is faster, your method or one similar to this. I'd suspect this one to start being a lot faster as the size of the string grew. In that case, you may want to use the table version below.

Note, in your OP you stated "without special consideration to case". This method, since it uses ASCII conversions, is respective to case. However... this can be altered by using UPPER or LOWER to the entire string to maintain consistency.

DECLARE @PLAIN_TEXT nvarchar(MAX) = N'HelloWorld'

--split your string into a column, and compute the decimal value (N) 
if object_id('tempdb..#staging') is not null drop table #staging
select 
    substring(a.b, v.number+1, 1) as Val
    ,ascii(substring(a.b, v.number+1, 1)) as N
    --,row_number() over (order by (select null)) as RN
into #staging
from (select @PLAIN_TEXT b) a
    inner join
         master..spt_values v on v.number < len(a.b)
where v.type = 'P' 

--select * from #staging


--create a fast tally table of numbers to be used to build the ROT-47 table.

;WITH
    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
    cteTally(N) AS 
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
    )

----uncomment this out to see the encrypted mapping...
--select
--  s.Val
--  ,s.N
--  ,e.ENCRYPTED_TEXT
--from #staging s
--left join(
--  select 
--  N as DECIMAL_VALUE
--  ,char(N) as ASCII_VALUE
--  ,case 
--      when 47 + N <= 126 then char(47 + N)
--      when 47 + N > 126 then char(N-47)
--  end as ENCRYPTED_TEXT
--  from cteTally
--  where N between 33 and 126) e on e.DECIMAL_VALUE = s.N


--Here we put it all together with stuff and FOR XML
select 
    PLAIN_TEXT = @PLAIN_TEXT
    ,ENCRYPTED_TEXT =
        stuff((
        select
            --s.Val
            --,s.N
            e.ENCRYPTED_TEXT
        from #staging s
        left join(
        select 
            N as DECIMAL_VALUE
            ,char(N) as ASCII_VALUE
            ,case 
                when 47 + N <= 126 then char(47 + N)
                when 47 + N > 126 then char(N-47)
            end as ENCRYPTED_TEXT
        from cteTally
        where N between 33 and 126) e on e.DECIMAL_VALUE = s.N
        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 0, '')



drop table #staging

USING A TABLE OF VALUES TO CIPHER

declare @table table (ID int, PLAIN_TEXT nvarchar(4000))
insert into @table
values
(1,N'HelloWorld'),
(2,N'AnotherWord'),
(3,N'SomeNewWord')

--split your string into a column, and compute the decimal value (N) 
if object_id('tempdb..#staging') is not null drop table #staging
select 
    substring(a.b, v.number+1, 1) as Val
    ,ascii(substring(a.b, v.number+1, 1)) as N
    --,dense_rank() over (order by b) as RN
    ,a.ID
into #staging
from (select PLAIN_TEXT b, ID FROM @table) a
    inner join
         master..spt_values v on v.number < len(a.b)
where v.type = 'P' 

--select * from #staging


--create a fast tally table of numbers to be used to build the ROT-47 table.

;WITH
    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
    cteTally(N) AS 
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
    )



--Here we put it all together with stuff and FOR XML
select 
    PLAIN_TEXT
    ,ENCRYPTED_TEXT =
        stuff((
        select
            --s.Val
            --,s.N
            e.ENCRYPTED_TEXT
        from #staging s
        left join(
        select 
            N as DECIMAL_VALUE
            ,char(N) as ASCII_VALUE
            ,case 
                when 47 + N <= 126 then char(47 + N)
                when 47 + N > 126 then char(N-47)
            end as ENCRYPTED_TEXT
        from cteTally
        where N between 33 and 126) e on e.DECIMAL_VALUE = s.N
        where s.ID = t.ID
        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 0, '')
    from @table t