Incrementing Character value in T-sql

Question

I have 2 set of values in a column i.e first 4 character are characters and next 4 character are numeric. Ex:AAAA1234 Now I have to increment the value from right end i.e when numeric value reached 9999 then I have to increment character by 1 character.

Sample : Consider the last value stored in a column is AAAA9999 then next incremented values should be in a sequence AAAB9999,....... AABZ9999,..... BZZZ9999..... ZZZZ9999(last value). And when it reaches ZZZZ9999 then I have to reset the value to AAAA0001.

How can do it in T-SQL ???

Answer 1

Here is a scalar select function that do the increment.

CREATE FUNCTION dbo.inc_serial( @id char(8) )
RETURNS char(8) BEGIN

select @id = case when SUBSTRING(id,2,1) <> '[' then id else STUFF( id, 1, 2, char(((ascii(id)+1-65)%26)+65) + 'A' ) end from (
select case when SUBSTRING(id,3,1) <> '[' then id else STUFF( id, 2, 2, char(ascii(right(id,7))+1) + 'A' ) end as id from (
select case when SUBSTRING(id,4,1) <> '[' then id else STUFF( id, 3, 2, char(ascii(right(id,6))+1) + 'A' ) end as id from (
select 
    case when right(@id,4) < '9999'
    then concat( left(@id,4), right(concat( '000', (cast(right(@id,4) as smallint)+1) ), 4 ) )
    else concat( left(@id,3), char(ascii(right(@id,5))+1), '0001' ) end as id
) t1 ) t2 ) t3
RETURN @id

END

Basically, the code just add one to the number, and repeatingly carring overflow up to the left.

If your table always has one and only one row to be updated (e.g. an option/flag table):

UPDATE [table] SET [serial] = dbo.inc_serial( [serial] );

If your table has multiple rows, you will need an identity or high precision creation time column, so that we know where to continue from after reset.

INSERT INTO [table] (serial) VALUES ( dbo.inc_serial((
   select top 1 case when count(*) > 0 then max([serial]) else 'AAAA0000' end AS id 
   from [table] where [id] = ( select max([id]) from [table] )
)));

For concurrency safety, use XLOCK,ROWLOCK,HOLDLOCK to lock the table. They are obmitted from the examples for simplicity.

---

If you do not like udf, you can embedded the query inline.

An inline example for first case:

UPDATE [table] SET [serial] = ((
select case when SUBSTRING(id,2,1) <> '[' then id else STUFF( id, 1, 2, char(((ascii(id)+1-65)%26)+65) + 'A' ) end as id from (
select case when SUBSTRING(id,3,1) <> '[' then id else STUFF( id, 2, 2, char(ascii(right(id,7))+1) + 'A' ) end as id from (
select case when SUBSTRING(id,4,1) <> '[' then id else STUFF( id, 3, 2, char(ascii(right(id,6))+1) + 'A' ) end as id from (

select 
    case when right(id,4) < '9999'
    then concat( left(id,4), right(concat( '000', (cast(right(id,4) as smallint)+1) ), 4 ) )
    else concat( left(id,3), char(ascii(right(id,5))+1), '0001' ) end as id
from (
    select top 1 [serial] as id from [table] with (XLOCK,ROWLOCK,HOLDLOCK)
) t0
) t1 ) t2 ) t3

))

The function can also be written as an inline table value function for better performance, at cost of more complex usage, but I would not border unless it frequently runs on multiple rows.

Answer 2

With the help of PATINDEX,SUBSTRING,ASCII functions you can achieve your special cases.
(I have found the solution for your special cases). Likewise you can add your own addition feature.

create table #temp(col1 varchar(20))

insert into #temp values('AAAA9999')
insert into #temp values('AAAZ9999')
insert into #temp values('AAZZ9999')
insert into #temp values('AZZZ9999')
insert into #temp values('ZZZZ9999')

select * from #temp
select col1,
case when cast(substring(col1,patindex('%[0-9]%',col1),len(col1)) as int) = 9999 and left(col1,4) <> 'ZZZZ'
then 
    case 
    when substring(col1,(patindex('%[0-9]%',col1)-1),1) <> 'Z' then left(col1,3)+char(ASCII(substring(col1,(patindex('%[0-9]%',col1)-1),1)) + 1)+right(col1,4)
    when substring(col1,(patindex('%[0-9]%',col1)-2),1) <> 'Z' then left(col1,2)+char(ASCII(substring(col1,(patindex('%[0-9]%',col1)-2),1)) + 1)+right(col1,5)
    when substring(col1,(patindex('%[0-9]%',col1)-3),1) <> 'Z' then left(col1,1)+char(ASCII(substring(col1,(patindex('%[0-9]%',col1)-3),1)) + 1)+right(col1,6)
    when substring(col1,(patindex('%[0-9]%',col1)-4),1) <> 'Z' then char(ASCII(substring(col1,(patindex('%[0-9]%',col1)-4),1)) + 1)+right(col1,7)
    end
    else 'AAAA0001'
end as outputofcol1
--patindex('%[0-9]%',col1)-1 as charpos,
--substring(col1,(patindex('%[0-9]%',col1)-1),1) as substr4,
--substring(col1,(patindex('%[0-9]%',col1)-2),1) as substr3,
--substring(col1,(patindex('%[0-9]%',col1)-3),1) as substr2,
--substring(col1,(patindex('%[0-9]%',col1)-4),1) as substr1
--ASCII(substring(col1,(patindex('%[0-9]%',col1)-1),1)) as ASC_value
from #temp 

Answer 3

The following function should return the desired value:

    IF OBJECT_ID (N'dbo.ufnGetIndexValue') IS NOT NULL
    DROP FUNCTION dbo.ufnGetIndexValue;
GO

CREATE FUNCTION dbo.ufnGetIndexValue(@MainString CHAR(8))
RETURNS CHAR(8)
AS
BEGIN
DECLARE @NumberPart INT
DECLARE @StringPart CHAR(4)
DECLARE @Position TINYINT
DECLARE @char CHAR

SET @NumberPart=CONVERT(INT,SUBSTRING(@MainString,5,8))
SET @StringPart=SUBSTRING(@MainString,1,4)

IF @NumberPart=9999
BEGIN
    SET @NumberPart=1111;
    SET @Position=4
    WHILE @Position >= 1
    BEGIN
        SET @char=SUBSTRING(@StringPart,@Position,1)
        IF(@char!='Z')
        BEGIN
            SET @char=CHAR(ASCII(@char)+1);
            SET @StringPart = STUFF(@StringPart,@Position,1,@char);
            BREAK;
        END
        SET @StringPart = STUFF(@StringPart,@Position,1,'A');
        SET @Position-=1;
    END
END
ELSE
BEGIN
    SET @NumberPart+=1;
END

SET @MainString=@StringPart+CAST(@NumberPart AS CHAR(4));
RETURN @MainString
END
GO

Answer 4

Here is a conceptual script, which does what you want. You will need to tweak it to suit your requirements

DECLARE @test table(TestValue char(8))
DECLARE @CharPart char(4),@NumPart int
SET @CharPart = 'AAAA'
SET @NumPart = 1
WHILE @NumPart <=9999
BEGIN
INSERT INTO @test
SELECT @CharPart+RIGHT(('0000'+CAST(@NumPart AS varchar(4))),4)
IF @NumPart = 9999
    BEGIN
    IF SUBSTRING(@CharPart,4,1)<>'Z'
        BEGIN
        SET @CharPart = LEFT(@CharPart,3)+CHAR(ASCII(SUBSTRING(@CharPart,4,1))+1)
        SET @NumPart = 1
        END
    ELSE IF SUBSTRING(@CharPart,4,1)='Z' AND SUBSTRING(@CharPart,3,1) <>'Z'
        BEGIN
        SET @CharPart = LEFT(@CharPart,2)+CHAR(ASCII(SUBSTRING(@CharPart,3,1))+1)+RIGHT(@CharPart,1)
        SET @NumPart = 1
        END 
    ELSE IF SUBSTRING(@CharPart,3,1)='Z' AND SUBSTRING(@CharPart,2,1) <>'Z'
        BEGIN
        SET @CharPart = LEFT(@CharPart,1)+CHAR(ASCII(SUBSTRING(@CharPart,2,1))+1)+RIGHT(@CharPart,2)
        SET @NumPart = 1
        END 
    ELSE IF SUBSTRING(@CharPart,1,1)<>'Z' 
        BEGIN
        SET @CharPart = CHAR(ASCII(SUBSTRING(@CharPart,1,1))+1)+RIGHT(@CharPart,3)
        SET @NumPart = 1
        END
    ELSE IF SUBSTRING(@CharPart,1,1)='Z' 
        BEGIN
        SET @CharPart = 'AAAA'
        SET @NumPart = 1
        INSERT INTO @test
        SELECT @CharPart+RIGHT(('0000'+CAST(@NumPart AS varchar(4))),4)
        BREAK
        END 
    END
ELSE
    BEGIN
    SET @NumPart=@NumPart+1
    END
END

SELECT * FROM @test