Shift ascii code in MS SQL

Question

I'd like to Shift (add) 4 values in the column. Basically add 4 to each character ASCII number. F.e.:

'ABCD' -> 'EFGH'
'1234' -> '5678'
'A1B2' -> 'E5F6'

I can change all characters to hex code like this,

SELECT CONVERT(VARBINARY(MAX), CONVERT(VARCHAR(MAX), Column1))
FROM Table1

but I don't know how to add 4 for each characters.

Would it be possible with the query?

Samples:

Z -> ^
z -> ~
W -> [
9 -> =

Something like CHAR(ASCII('z') + 4) = ~

You could write a function that goes through a string character by character and, say, increases the ascii value of that character by 4, but I don't think there's an easy way to do this in SQL. — ZLK, Oct 05 '16 at 04:52
What would be the scene for `W`, `X`, `Y`, `Z`, `6`, `7`, `8`, `9`? — Ullas, Oct 05 '16 at 04:53
Z -> ^ , z -> ~ and other special characters are not needed to be considered — sugy21, Oct 05 '16 at 05:21
@ZLK Could you please make a sample function that i can apply? — sugy21, Oct 05 '16 at 05:23

score 1 · Answer 1 · answered Oct 05 '16 at 05:28

This thing can be done using function (UDF)

sample

create  function Charchange(@inputstring varchar(max))
returns varchar(max)
AS
BEGIN
  DECLARE @i int, 
          @Results varchar(max)
  SET @Results=''
  SET @i = 1
  WHILE @i <= DATALENGTH(@inputstring)
    BEGIN
           SET @Results =  @Results + char(ASCII(SUBSTRING(@inputstring,@i,1))+4)

      SET @i=@i + 1
    END
RETURN @Results
END


select dbo.Charchange('abc123xyz')

 or 

  select dbo.Charchange(coulmn1) FROM Table1

score 1 · Answer 2 · edited May 23 '17 at 10:34

1

-- Sample table
declare @T table(S varchar(10));

-- Sample data
insert into @T(S) values ('ABCD'),('1234'),('A1B2'),('WXYZ');

-- Split the string using a numbers table and
-- Rebuild the string with for xml path
select T.S, 
       (
       select char(ascii(substring(T.S, N.N, 1)) + 4)
       from dbo.Number as N
       where N.N >= 1 and 
             N.N <= len(T.S) 
       order by N.N
       for xml path(''),  type
       ).value('text()[1]', 'varchar(10)')
from @T as T;

SQL, Auxiliary table of numbers

Concatenate many rows into a single text string

edited May 23 '17 at 10:34

Community

1
1

answered Oct 05 '16 at 06:56

Mikael Eriksson

136,425
22
210
281

Your solution inspired me to rewrite my answer in more efficient way without XML transformation :) – gofr1 Oct 05 '16 at 08:22

gofr1 · Answer 3 · 2016-10-05T07:34:25.933

I got solution with recursive CTE and FOR XML PATH:

DECLARE @n int = 4 --Add 4 for each ASCII
--Here I simulate your table, hope you have ids in it
-- because I used to join it with values
;WITH YourTable AS (
SELECT  CAST(SomeString as nvarchar(max)) as SomeString
FROM (VALUES ('ABCD'),('1234'),('A1B2'),('WXYZ')
) as t(SomeString)
), cte AS (--Here we replace chars
SELECT  CHAR(ASCII(SUBSTRING(SomeString,1,1))+@n) as d,
        1 as [level],
        LEN(SomeString) as l,
        SomeString as OrigString
FROM YourTable
UNION ALL
SELECT  CHAR(ASCII(SUBSTRING(OrigString,[level]+1,1))+@n),
        [level]+1,
        l,
        OrigString
FROM cte
WHERE l >= [level]+1)
--Final output
SELECT DISTINCT c.OrigString,
                (SELECT d+''
                FROM cte
                WHERE c.OrigString = OrigString
                FOR XML PATH('')
                ) as NewString
FROM cte c
OPTION (MAXRECURSION 0)

Output:

OrigString  NewString
1234        5678
A1B2        E5F6
ABCD        EFGH
WXYZ        [\]^

EDIT

Solution with VARBINARY(MAX) transformations:

DECLARE @x xml,
        @n int = 4 --Add 4 for each ASCII
--Here I simulate your table, hope you have ids in it
-- because I used to join it with values
;WITH YourTable AS (
SELECT  CAST(SomeString as nvarchar(max)) as SomeString
FROM (VALUES ('ABCD'),('1234'),('A1B2'),('WXYZ')
) as t(SomeString)
)
SELECT @x = (
    SELECT CAST('<row str="'+SomeString+'"><p>'+REPLACE(REPLACE(CONVERT(nvarchar(max),CONVERT(VARBINARY(MAX),SomeString),1),'0x',''),'00','</p><p>')+'</p></row>' as xml)
    FROM YourTable
    FOR XML PATH('')
)
;WITH cte AS(
SELECT  t.c.value('../@str','nvarchar(max)') as OrigString,
        CHAR(CAST(CONVERT(VARBINARY(2),'0x'+t.c.value('.','nvarchar(2)'),1) as int)+@n) as NewValues
FROM @x.nodes('/row/p') as t(c)
)

SELECT  DISTINCT 
                c.OrigString,
                LEFT((
                SELECT NewValues +''
                FROM cte
                WHERE OrigString = c.OrigString
                FOR XML PATH('')
                ),LEN(c.OrigString)) as NewString
FROM cte c

Same output.

Shift ascii code in MS SQL

3 Answers3