1
\$\begingroup\$

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 ?

\$\endgroup\$
1
\$\begingroup\$

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
\$\endgroup\$
  • \$\begingroup\$ Thank you for your answer. I edited my question to copy and paste the example for the statement "without special consideration to case". It should be more clear like this. I'm not used to temporary tables but your solution looks good. Would be interesting to run some tests to see which one is faster. Will see if I can take the time to do that. I'm going to wait a bit longer before validating your answer in case someone else want to give another answer. ;) \$\endgroup\$ – Aleph0 May 31 '17 at 20:22
  • \$\begingroup\$ @Aleph0 will you be running this on a lot of rows from a table? I could do some bench-marking in my environment if that's the case. \$\endgroup\$ – scsimon May 31 '17 at 20:30
  • \$\begingroup\$ No don't worry, it won't run a lot. I just thought it would be interesting to know which way is the fastest even if I won't run it on a lot of rows but don't take time to do some bench-marking, it is not necessary. \$\endgroup\$ – Aleph0 Jun 1 '17 at 6:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.