# ROT47 function implementation

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 ?

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

• 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. ;) May 31, 2017 at 20:22
• @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.
– S3S
May 31, 2017 at 20:30
• 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. Jun 1, 2017 at 6:34