4

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.

7
  • 3
    Does Z become A?
    – gbn
    Aug 13, 2013 at 7:39
  • 1
    Also is the only content in those columns upper case A-Z and 0-9? Aug 13, 2013 at 7:52
  • 1
    yes Z becomes A...A becomes B,B becomes C......And 9 becomes 0 ...and there are only A-Z and 0-9... Aug 13, 2013 at 7:52
  • Do both columns have a fixed format as showed in your sample or data length is variable?
    – peterm
    Aug 13, 2013 at 8:08
  • 2
    Ok, I'm not going to ask why, I am going to say if this is being done to scramble your data for say testing or really anything else it's a REALLY bad idea. That particular method is so transparent it's almost better just to leave it alone. Aug 13, 2013 at 11:41

2 Answers 2

7

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.]))
4
  • Thanks a lot This works fine ..... But still one question ... when i m updating the function is converting space to next character....so i have added this case when s.c = char(32) then ' ' .... but still showing some other characters... Aug 13, 2013 at 13:03
  • @RajniPatel You said in a comment "there are only A-Z and 0-9" so I based my answer on that. What would you like to do with characters that is not A-Z or 0-9? I have updated they answer where the are kept as is. Aug 13, 2013 at 13:36
  • numbers table link requires login :( Jun 29, 2018 at 20:15
  • @jason the blog post is gone. Updated with a link to a so question instead. Jun 29, 2018 at 20:38
2

Solution proposed by Mikael is absolutely great, but another option would be to use CLR function (although this is NOT the best approach for many reasons).

You can create a .NET class library (DLL) with similar code:

using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using System.Text;

public class NextCharacter
{
    [SqlFunction(DataAccess = DataAccessKind.None)]
    public static string Replace(string inputString)
    {
        StringBuilder output = new StringBuilder();
        char[] charTable = inputString.ToCharArray();

        foreach (char c in charTable)
        {
            char x;
            switch (c)
            {
                case '9':
                    x = '0';
                    break;
                case 'Z':
                    x = 'A';
                    break;
                case 'z':
                    x = 'a';
                    break;
                default:
                    x = (char)(Convert.ToUInt16(c) + 1);
                    break;
            }
            output.Append(x);
        }
        return output.ToString();
    }
}

then you'd need to enable CLR integration on your instance

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

and then just deploy the assembly to your database:

CREATE ASSEMBLY NextCharacter from 'C:\NextCharacter.dll'
GO

CREATE FUNCTION NextCharacter(@input nvarchar(max))
RETURNS nvarchar(max)
AS EXTERNAL NAME NextCharacter.NextCharacter.Replace; 
GO

it will work just like a regular function:

SELECT dbo.NextCharacter('test');
GO

UPDATE dbo.YourTable SET 
     [Name] = dbo.NextCharacter([Name])  
    ,[Passport No.] = dbo.NextCharacter([Passport No.]);
GO

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.