Need to change alphanumeric characters to next character

I have 2 columns Name, Passport No. in a table with more than 100k rows.

Name   Passport No.
KOP    A245
COS    C990

After the update it should look like this.

Name   Passport No.
LPQ    B356
DPT    D001

Update current character with the next character in the database. I don't want to select anything from table, I want to update the table.

Please don't ask me why.

Use a numbers table to split your string and a case statement to figure out what the values your characters should have and for xml path to rebuild the string.

Put the logic in a function so it can be reused for multiple columns.

create function dbo.SS(@S varchar(max))
returns table as return
(
  select
  (
    select case
            when S.C = '9' then '0'
            when S.C = 'Z' then 'A'
            when S.C like '[A-Z,0-9]' then char(ascii(S.C) + 1)
            else S.C -- Keep not A-Z,0-9 as is
          end  
    from Number as N
      cross apply (select substring(@S, N.N, 1)) as S(C )
    where N.N between 1 and len(@S)
    for xml path(''), type
  ).value('text()[1]', 'varchar(max)') as C
)

This would be your update statement.

update YourTable
set Name = (select C from dbo.CC(Name)),
    [Passport No.] = (select C from dbo.CC([Passport No.]))

StackExchange Database Administrators Q#48019, answer score: 7