Edit Question - KnowledgeHub

Title Body

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&#x20;simple&#x20;translation 2 hello&#x20;world 3 wow&#x20;you&#x20;ran&#x20;this But need: RowID readable ----------- --------------------------------------- 1 a simple translation 2 hello world 3 wow you ran this Is there 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.

Tags (comma-separated) Cancel