this code basically translates characters based on position in one string to the character at the same position in another string and it runs for all rows in the table.
when I run this (simplified version):
DECLARE @R char(40)
DECLARE @U char(40)
SET @R=' abcdefghijklmnopqrstuvwxyz!@#$%^&*()_+'+char(181)
SET @U=REVERSE(@R)
DECLARE @TestTable TABLE (RowID int identity(1,1) primary key, Unreadable varchar(500))
INSERT INTO @TestTable VALUES ('+µt$zw!*µsu+yt!+s$xy')
INSERT INTO @TestTable VALUES ('%*!!xµpxu!(')
INSERT INTO @TestTable VALUES ('pxpµnxrµu+yµs%$t')
;WITH CodeValues AS
(
SELECT
Number,SUBSTRING(@R,Number,1) AS R,ASCII(SUBSTRING(@U,Number,1)) AS UA
FROM Numbers
WHERE Number<=LEN(@R)
)
SELECT
t.RowID
,(SELECT
''+c.R
FROM Numbers n
INNER JOIN CodeValues c ON ASCII(SUBSTRING(t.Unreadable,n.Number,1))=c.UA
WHERE n.Number<=LEN(t.Unreadable)
FOR XML PATH('')
) AS readable
FROM @TestTable t
I get the following:
RowID readable
----------- ---------------------------------------
1 a simple translation
2 hello world
3 wow you ran this
but need:
RowID readable
----------- ---------------------------------------
1 a simple translation
2 hello world
3 wow you ran this
Is these any way, other than REPLACE(), to have the spaces show up properly? This also happens on line breaks, in my actual code.
Can this be rewritten in a better way? I basically just used the FOR XML PATH('') to concatenate the individual row values together.