February 9, 2011 at 7:49 am
Hi,
I have a table as part of a bespoke application. The identity column is propulated by the software (it is not flagged as being an identity column in SQL).
It increments using the following sequence 0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ
For example
KA0
KA1
KA2
...
KAZ
KB0
KB1
Is this a standard format? Is there an easy way to get the next in series?
I wrote the following which appears to work but is there a better way to do it
DECLARE
@seq CHAR(36),
@id CHAR(3),
@p INT
Set @seq = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
PRINT @seq
SELECT @id = 'KBC'
SET @p = CHARINDEX(RIGHT(@id,1),@seq,1)
IF@p <> '36'
BEGIN
SELECT
@p = @p + 1,
@id = LEFT(@id,2) + SUBSTRING(@seq,@p,1)
PRINT @id
RETURN
END
IF@p = '36'
BEGIN
SELECT @p = CHARINDEX(SUBSTRING(@id,2,1),@seq,1)
print @p
IF@p <> '36'
BEGIN
SELECT
@p = @p + 1,
@id = LEFT(@id,1) + SUBSTRING(@seq,@p,1) + '0'
PRINT @id
RETURN
END
IF@p = '36'
BEGIN
SELECT @p = CHARINDEX(SUBSTRING(@id,1,1),@seq,1)
print @p
END
IF@p <> '36'
BEGIN
SELECT
@p = @p + 1,
@id = SUBSTRING(@seq,@p,1) + '00'
PRINT @id
RETURN
END
END
February 9, 2011 at 11:23 am
To my knowledge there's nothing standard that'd do that for you. I've written similar functions in the past to do incrementing that included letters. Here's one of them that was very close (I chopped it up a bit to match your scenario).
You can compare them for speed and accuracy if you'd like.
/*
=============================================================================================
CREATE DATE: 05/22/2009
LAST MODIFIED:05/22/2009
CREATED BY:SETH PHELABAUM
PURPOSE:Generates the next Alpha-Numeric code in sequence.
NOTES:Will not re-use or fix gaps. If you manually insert a gap, you could be wasting thousands of codes.
ASCII Numbers 65-90 are A-Z. ASCII 48-57 are 0-9.
ISSUES:If an ANC ZZZ or higher ASCII value is put into the system, this thing will break. (Out of valid Numbers)
It will still return something, but it will use bogus characters for A1.
This would likely be sped up significantly if the MAX() was done outside of the function.
=============================================================================================
SELECT dbo.fn_GenNextANC()
*/
ALTER FUNCTION fn_GenNextANC()
RETURNS char(3)
AS
BEGIN
DECLARE @MaxANC char(3),
@A1int,
@A2 int,
@A3 int,
@NewANCchar(3)
--SET @MaxANC = RIGHT( LTRIM(RTRIM((SELECT MAX(ANCField) FROM YourTable))), 3)
SET @MaxANC = 'KAZ'
SELECT @A1 = ASCII(LEFT(@MaxANC,1)),
@A2 = ASCII(LEFT(RIGHT(@MaxANC,2),1)),
@A3 = ASCII(RIGHT(@MaxANC,1))
IF @A3 < 90 -- Increments A3 by 1.
SET @NewANC = (char(@A1) +
char(@A2) +
CASE WHEN @A3 = 57 THEN char(@A3+8) ELSE char(@A3+1) END ) -- Uses Numbers, skips bogus characters.
ELSE
BEGIN
IF @A2 < 90 -- If A2 < 'Z', add 1 and use 0 for A3.
SET @NewANC = (char(@A1) +
CASE WHEN @A2 = 57 THEN char(@A2+8) ELSE char(@A2 + 1) END + -- Uses Numbers, skips bogus characters.
char(48))
ELSE
SET @NewANC = (-- If A2 = 'Z', Increment A1 by 1, Use 0 for A2 and A3.
CASE WHEN @A1 = 57 THEN char(@A1+8) ELSE char(@A1+1) END + -- Uses Numbers, skips bogus characters.
char(48) +
char(48))
END
RETURN(@NewANC)
END
February 10, 2011 at 7:47 am
Many Thanks Garadin
Yours looks a better way of working so I've amended it for my needs.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply