CODEWITHSUNDEEP
sql-serversql-server-2005increment
user2633770
user2633770

Reputation: 9

Increment Numbers with Alphabets In SQL Server Table

My Table structure is

id    type   no     amount
1     type1  a1      1000
2     type1  a2      2000
3     type2  b1      3000
4     type3  c1      4000
5     type1  a3      5000
6     type2  b2      6000
7     type2  b3      7000
8     type3  c2      8000

now i wants to increment the no field data based on the type.

for example for type1 the next no is a4

and

for numeric only I am using the following code

SELECT ISNULL(Max(No),0)+1 AS No FROM table

but how to do it for with Alphabets in SQL Server 2005

Upvotes: 0

Views: 2465

Answers (3)

i-one
i-one

Reputation: 5120

Assuming that prefixes are of single character length, you may try following:

;with cte as (
    select type, typePrefix = left(no, 1), typeNum = right(no, len(no) - 1)
    from TableName
)
select typePrefix + cast(isnull(max(typeNum), 0) + 1 as varchar(10))
from cte
where type = 'type1'
group by typePrefix

But it will not work if you try to generate next no for a type which is not in table (e.g. 'type4'). To allow it, you may need a separate table, where prefix for each type is specified:

create table TypePrefixes (type varchar(50), prefix varchar(10))
insert into TypePrefixes values ('type1', 'a')
insert into TypePrefixes values ('type2', 'b')
insert into TypePrefixes values ('type3', 'c')
insert into TypePrefixes values ('another_type', 'd')
--etc.

In this case, statement to get next no will look as:

select tp.prefix + cast(isnull(max(cast(right(t.no, len(t.no) - len(tp.prefix)) as int)), 0) + 1 as varchar(20))
from TableName t
    right join TypePrefixes tp on tp.type = t.type
where tp.type = 'type4'
group by tp.prefix

Also, you may just wish to calculate no for each record on the fly, like:

;with cte as (
    select *,
        typeNum = row_number() over (partition by type order by id),
        typePrefix = char(dense_rank() over (order by type) + ascii('a') - 1)
    from TableName
)
select *, No2 = typePrefix + cast(typeNum as varchar(10))
from cte

However, the latter is limited in number of distinct types in your table, which should not exceed 26 (so that we not go beyond 'z').

Upvotes: 1

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

First, you need an UNIQUE index on No column:

CREATE UNIQUE INDEX IUN_MyTable_On
ON MySchema.MyTable(On);
GO

This unique index will prevent duplicate values but, also, will help the query below.

Second, you could use this script to generate the next No for a given letter:

DECLARE @Chr CHAR(1);
SET @Chr='A';

BEGIN TRY

BEGIN TRANSACTION;

DECLARE @LastId INT;
DECLARE @NewNo VARCHAR(...); -- Fill with No's max. length

-- Previous index will help this query
SELECT @LastId=MAX( CONVERT(INT,SUBSTRING(@LastNo,2,8000)) )
FROM MySchema.MyTable x WITH(UPDLOCK) -- It locks the rows to prevent a concurent session to generate the same value (No)
WHERE x.No LIKE @Chr+'%';

SET @NewNo=@Chr+CONVERT(VARCHAR(11),ISNULL(@LastId,0)+1);

-- Do whatever you want with the new value: ex. INSERT
INSERT INTO ... (No,...)
VALUES (@NewNo,...);

COMMIT TRANSACTION;

END TRY
BEGIN CATCH
DECLARE @ErrMsg NVARCHAR(2000);
SET @ErrMsg=ERROR_MESSAGE();
IF @@TRANCOUNT>0
BEGIN
    ROLLBACK;
END
RAISERROR(@ErrMsg,16,1);
END CATCH

Note #1: This solution should be safe if this is the only way to generate the new values (@NewNo).

Note #2: If that SELECT query acquires at least 5000 locks then SQL Server will escalate locks at table/partition level.

Upvotes: 0

markS
markS

Reputation: 580

try something like

SELECT ISNULL(Max(No),0)+1 AS No FROM table group by type

Upvotes: 0

Related Questions