How to increment a varchar?
-
Hello,
I searched for this but couldnt find a solution. I need a way to increment a varchar field automatically. This field is a varchar(17) and holds both alpha and numeric characters. It is basically an identity field, and needs to be unique throughout the db, but was not created to enforce uniqueness (Not my database design). My values look like this "000000000000002Ww", the increment is first the numeric values, then lowercase alpha, followed by uppercase. 0-9 then lowercase "a", capital "A", lowercase "b" and so on. I am not an expert yet so if this has been answered elsewhere please point me in the right direction.
Here is a sample sequence.
000000000000002W9
000000000000002Wa
000000000000002WA
000000000000002Wb
Thank you.
-
is your database case sensative ?
Kindest Regards,Amit Lohia
-
Hi Aaron,
You request reminded me of this article...
http://www.sqlteam.com/item.asp?ItemID=1417
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Yes. Basically this field is a Base 62 number that I need to auto-increment. I have not found a way to do this yet without some external programming, which is not available for my database.
-
This Base64 encode and decode was posted here a while ago. It might have something you can use.
http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=1520
And then again, I might be wrong ...
David Webb -
Thank you Ryan and David, I will dig into the links. Wish me luck. If you can think of anything else I'll keep checking.
Again thank you.
-
Good luck
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Well - I will certainly wish you luck for I think you could use all that you can get..
so you'd have to sort first on this varchar field to get max(id) before incrementing...how are "gaps" handled in a case like this ?!?!
**ASCII stupid question, get a stupid ANSI !!!** -
The first thing I have to say is a Sequence is an object while Identity is a property, so here is a Microsoft Provided solution. I think the Sequence is stored in a row which may relate to the differences of a property and an object I am not sure. Both are defined in ANSI SQL, but Oracle and Microsoft implemented only one and not both. Hope this helps.
http://blogs.msdn.com/sqlcat/archive/2006/04/10/572848.aspx
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie -
Hi,
I just posted this article on this wonderfull site, not sure if it helps you 100%! However, I do hope it will sure give you some ideas to nail down your problem.
http://www.sqlservercentral.com/columnists/aSayed/generatingasequentialpattern.asp
Regards,
Asif Sayed
-
First, create a translation table... here's the code...
--===== Create the translation table and seed with
-- 62 numbers from 0 to 61
-- (10 digits + 26 Lower Case + 26 Upper Case)
SELECT TOP 62
IDENTITY(TINYINT,0,1) AS Number,
CAST(NULL AS CHAR(1)) AS Letter
INTO dbo.Translate
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
--===== Create the lower case letters
UPDATE dbo.Translate
SET Letter = CHAR(ASCII('a')+(Number/2)-5)
WHERE Number%2 = 0
--===== Create the upper case letters
UPDATE dbo.Translate
SET Letter = CHAR(ASCII('A')+((Number-1)/2)-5)
WHERE Number%2 = 1
--===== Create the numbers 0-9
UPDATE dbo.Translate
SET Letter = STR(Number,1)
WHERE Number BETWEEN 0 AND 9
--===== Update the AsciiVal column
UPDATE dbo.Translate
SET AsciiVal = ASCII(Letter)
...then, play with this... when you're sure that it's working correctly, turn it into a function.
DECLARE @Number DECIMAL(38)
SET @Number = 9999999999999999999999999999999 --largest possible number for this
DECLARE @Counter DECIMAL(38)
SET @Counter = 17
DECLARE @String VARCHAR(18)
SET @String = ''
DECLARE @Letter CHAR(1)
WHILE @Counter >= 0
BEGIN
SELECT @String = @String + Letter
FROM Translate
WHERE Number = FLOOR(CAST(@Number/POWER(62.0,@Counter) AS FLOAT))
SELECT @Number =
CASE
WHEN @Number - (FLOOR(CAST(@Number/POWER(62.0,@Counter) AS FLOAT)))*POWER(62.0,@Counter) > 0.0
THEN @Number - (FLOOR(CAST(@Number/POWER(62.0,@Counter) AS FLOAT)))*POWER(62.0,@Counter)
ELSE @Number
END
SET @Counter = @Counter-1
END
SELECT @String
Rather than trying to increment such a strange numbering set, maintain a decimal number somewhere, increment that, then use the code above to convert it to what you wanted it to look like.
Next... kill the stupid SOB that designed this numbering set.
Finally, send me a case of beer because I already have enough pretzels.
By, the way, tell the dummy that designed the numbering system that it goes out to 18 characters, not 17.
Gimme some feedback on this one, please... it took a while to science it out and make it so it looks simple.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Great work, Jeff. I agree with your recommended approach, and that it's crazy design.
For fun, I thought I'd try to write it without the while loop...
--inputs
declare @x decimal(38)
set @x = 38555935228647999764
--set @x = 11107
--set @x = 9999999999999999999999999999999
--calculation
declare @numbers table (i int identity(0, 1), x bit)
insert @numbers select top 62 null from master.dbo.syscolumns a
declare @powers table (i int, p decimal(38, 0))
insert @powers select i, power(62.0, i) from @numbers where i < 21
declare @MaxCharacters int
select @MaxCharacters = isnull(max(i), -1) from @powers where @x >= p
declare @s-2 varchar(18) --the base 62 string we're going to construct
set @s-2 = replicate('0', 17 - @MaxCharacters)
declare @d int --divisor
select
@d = isnull(@x / p, 0),
@x = @x - p * @d,
@s-2 = @s-2 + case when @d BETWEEN 0 AND 9 then STR(@d,1)
when @d % 2 = 0 then CHAR(ASCII('a') + (@d / 2) - 5)
when @d % 2 = 1 then CHAR(ASCII('A') + ((@d - 1) / 2) - 5)
end
from @powers p where i <= @MaxCharacters order by i desc
select @s-2
BTW, Jeff - 62 (for example) doesn't quite work for you, although it's pretty trivial to fix. And I get a slightly different result for you for 999... - and I'm not sure which is right (or why)
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Use this function in a computed column that takes an Identity column as its input:
Create Function dbo.WeirdIdentity
(
@Num BigInt
)
Returns VarChar(256)
As Begin
Declare
@Digits Char(36),
@Results Char(17),
@i Int,
@j-2 Int
Set @Digits='0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
If @Num<Len(@Digits) Begin
Return SubString(@Digits,@Num+1,1)
End
Return dbo.WeirdIdentity(@Num/Len(@Digits))+SubString(@Digits,(@Num%Len(@Digits))+1,1)
End
PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be. -
Wow, you folks are brilliant. I havent had a chance to test them out yet, but thank you very much. And Jeff, trust me, I wish I was involved in the planning of this database. But we have to deal with what we are given.
Again, thank you. Ya'll are brilliant.
-
As Ryan pointed out... had a little bug in my code and repaired it. Managed to condense it a bit, too. Still takes a very pigish 16 mintues to run on a million rows even in light of idexes but I'll post it anyway...
CREATE FUNCTION dbo.WierdNum(@Number DECIMAL(38))
RETURNS VARCHAR(18)
AS
BEGIN
DECLARE @Counter DECIMAL(38)
SET @Counter = 17
DECLARE @String VARCHAR(18)
SET @String = ''
WHILE @Counter >= 0
BEGIN
SELECT @String = @String + Letter,
@Number = @Number - (FLOOR(CAST(@Number/POWER(62.0,@Counter) AS FLOAT)))*POWER(62.0,@Counter)
FROM Translate
WHERE Number = FLOOR(CAST(@Number/POWER(62.0,@Counter) AS FLOAT))
SET @Counter = @Counter-1
END
RETURN @String
END
Ryan,
Thanks for finding the problem... turn out I over-scienced this puppy. I gotta turn your's into a function and test it 'cause now you have me curious. Cute trick with the name thing... wrote a ditty to reverse engineer the magic number for my name as well... almost reminds me of the early attempts at code sheet encryption but this whole wierd numbering system is actually a bit more sophisticated in some aspects.
Peter, brilliant job of recursion... after making a minor change to make it match Aaron's original post, it looks like this and only takes a minute:29 to run on the same million rows as above...
Create Function dbo.WeirdIdentity
(
@Num BIGINT
)
Returns VarChar(256)
As Begin
Declare
@Digits Char(62),
@Results Char(17),
@i Int,
@j-2 Int
Set @Digits='0123456789aAbBcCdDeEfFgGhHiIjJkKlLmMnNoOpPqQrRsStTuUvVwWxXyYzZ'
If @Num<Len(@Digits)
Begin
Return SubString(@Digits,@Num+1,1)
End
Return dbo.WeirdIdentity(@Num/Len(@Digits))+SubString(@Digits,(@Num%Len(@Digits))+1,1)
End
go
Here's the call I used to get the leading zero's Aaron wanted...
SELECT RIGHT(REPLICATE('0',17)+dbo.WeirdIdentity(RowNum),17) FROM BigTest
Aaron,
Thanks for the feedback... I think that everyone who jumped in had some fun with this... tell the guy who came up with it that it would still be easier to just use GUID's with NEWID(). Takes fewer bytes and is globally unique... not just unique on the same server.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply