March 16, 2012 at 3:34 am
Jeff Moden (3/15/2012)
Charmer (3/15/2012)
SQLKnowItAll (3/15/2012)
Can you create a tally table with these values (if maximum is 2000, I would create 3000 just in case)?sorry friend,What is a tally table? i don't have much knowledge with SQL... if you give me an example , i would try to create that one...
See the following article. If you study it until you understand it, it will change your life insofar as T-SQL goes especially if you can see the possibilities beyond what is in the article.
i understand the tally table Jeff...it is simply superb and it would be very useful for my future works...
but my concern is we can't maintain distinct 2 length values or 1 length values if the row count exceeds more than the limit right?
its difficult to maintain if i am not wrong....for more than 2 length values 0 to 9999.... is enough for me because row count will not be exceeded more than 1000....
but i need values for length 1 and 2...
Thanks,
Charmer
March 16, 2012 at 7:05 am
Looks like you didn't look what -base encoding is. As you don't care of order in which values are incrementing, that is definitely would work for you.
As promised yesterday, here is code:
--DROP FUNCTION dbo.f_GetNextBased36Value
CREATE FUNCTION dbo.f_GetNextBased36Value (@value AS VARCHAR(50))
RETURNS VARCHAR(50) WITH SCHEMABINDING
AS
BEGIN
-- some variables
DECLARE @characters CHAR(36),
@iterator BIGINT = LEN(@value),
@multiplier BIGINT = 1,
@value10 BIGINT = 0,
@result VARCHAR(50);
-- encoding string and the default result
SELECT @characters = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
@result = '';
IF @value IS NULL SELECT @result = NULL, @value10 =NULL;
--decode value back to decimal
WHILE @iterator > 0
SELECT @value10 = @value10 + (( CHARINDEX(SUBSTRING(@value, @iterator, 1 ), @characters)-1) * @multiplier )
,@multiplier = @multiplier * 36
,@iterator = @iterator -1;
--increment the value
SET @value10 = @value10 + 1;
-- encode value back to Base36
WHILE @value10 > 0
SELECT @result = SUBSTRING(@characters, @value10 % 36 + 1, 1) + @result,
@value10 = @value10 / 36;
RETURN @result;
END
try it:
declare @val varchar(50) = '10', @i int = 0
while @i < 1500
begin
set @val = dbo.f_GetNextBased36Value(@val)
print @val
set @i=@i+1
end
You can see that one-digit Base36 encoded number can contain upto decimal 36, two-digits (from 10 to ZZ) will allow over 1000 in decimal...
If you wish you can pre-populate tally table using the above function.
March 16, 2012 at 9:56 am
Eugene Elutin (3/16/2012)
Looks like you didn't look what -base encoding is. As you don't care of order in which values are incrementing, that is definitely would work for you.As promised yesterday, here is code:
--DROP FUNCTION dbo.f_GetNextBased36Value
CREATE FUNCTION dbo.f_GetNextBased36Value (@value AS VARCHAR(50))
RETURNS VARCHAR(50) WITH SCHEMABINDING
AS
BEGIN
-- some variables
DECLARE @characters CHAR(36),
@iterator BIGINT = LEN(@value),
@multiplier BIGINT = 1,
@value10 BIGINT = 0,
@result VARCHAR(50);
-- encoding string and the default result
SELECT @characters = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
@result = '';
IF @value IS NULL SELECT @result = NULL, @value10 =NULL;
--decode value back to decimal
WHILE @iterator > 0
SELECT @value10 = @value10 + (( CHARINDEX(SUBSTRING(@value, @iterator, 1 ), @characters)-1) * @multiplier )
,@multiplier = @multiplier * 36
,@iterator = @iterator -1;
--increment the value
SET @value10 = @value10 + 1;
-- encode value back to Base36
WHILE @value10 > 0
SELECT @result = SUBSTRING(@characters, @value10 % 36 + 1, 1) + @result,
@value10 = @value10 / 36;
RETURN @result;
END
try it:
declare @val varchar(50) = '10', @i int = 0
while @i < 1500
begin
set @val = dbo.f_GetNextBased36Value(@val)
print @val
set @i=@i+1
end
You can see that one-digit Base36 encoded number can contain upto decimal 36, two-digits (from 10 to ZZ) will allow over 1000 in decimal...
If you wish you can pre-populate tally table using the above function.
Thanks a lot buddy..
it is really great...how about for 1 digit values...? what should i need to change this function for 1 digit value?
Thanks,
Charmer
March 16, 2012 at 10:01 am
You don't need to change anything.
It's not producing just 2-digit numbers
if you supply 0, the next value generated will be 1, one digit of Base36 will hold any decimal upto 36:
0,1,2,3, ...., 9,A,B,C,...,Z
If you try to increment Z, you will get 10, as there is nothing else available (until, you want to make everything case-sensitive, then you will be able to encode base64, or you can use non alphanumeric characters)
March 16, 2012 at 10:06 am
Eugene Elutin (3/16/2012)
You don't need to change anything.It's not producing just 2-digit numbers
if you supply 0, the next value generated will be 1, one digit of Base36 will hold any decimal upto 36:
0,1,2,3, ...., 9,A,B,C,...,Z
If you try to increment Z, you will get 10, as there is nothing else available (until, you want to make everything case-sensitive, then you will be able to encode base64, or you can use non alphanumeric characters)
yeah..so for 1 digit value we can't get too many value as like as 2 digit right?
Thanks,
Charmer
March 16, 2012 at 11:23 am
Charmer (3/16/2012)
Eugene Elutin (3/16/2012)
You don't need to change anything.It's not producing just 2-digit numbers
if you supply 0, the next value generated will be 1, one digit of Base36 will hold any decimal upto 36:
0,1,2,3, ...., 9,A,B,C,...,Z
If you try to increment Z, you will get 10, as there is nothing else available (until, you want to make everything case-sensitive, then you will be able to encode base64, or you can use non alphanumeric characters)
yeah..so for 1 digit value we can't get too many value as like as 2 digit right?
No comments...
March 4, 2013 at 4:28 pm
Charmer (3/15/2012)
SQLKnowItAll (3/15/2012)
The first problem is that @DecNumber is a BIGINT. You cannot store characters in BIGINT. First you need to change this to CHAR(2) or something that applies. Does the sequence matter? Can it start at AZ and then go to YD? Or are you eventually going to order this?ok i will change the data type...sequence is not important....maintaining with unique 2 digit values or 3 digit depends upon the need...it must fit for some large row counts(maximum 2000)...
DO NOT USE RANDOM 3 CHARACTER "words". The people that get the swear words have the right to sue you.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 4, 2013 at 4:36 pm
Jeff Moden (3/4/2013)
Charmer (3/15/2012)
SQLKnowItAll (3/15/2012)
The first problem is that @DecNumber is a BIGINT. You cannot store characters in BIGINT. First you need to change this to CHAR(2) or something that applies. Does the sequence matter? Can it start at AZ and then go to YD? Or are you eventually going to order this?ok i will change the data type...sequence is not important....maintaining with unique 2 digit values or 3 digit depends upon the need...it must fit for some large row counts(maximum 2000)...
DO NOT USE RANDOM 3 CHARACTER "words". The people that get the swear words have the right to sue you.
Which simply s***s. I'm sorry, there is no constitutional right to NOT be offended by something one says.
March 4, 2013 at 7:36 pm
True enough but in this lawsuit happy 21st century, it's gonna happen. We had a guy here in Michigan that got sued and lost. He was out in the woods near a stream and lost control of the canoe he was carrying and mashed a couple of fingers in the process. Of course, he did what any red blooded American boy did and swore like a sailor. It just so happened that some mother was hiking with her two kids and they all heard the tirade. She sued him based on an old law in Michigan and won. From what I understand, the Supreme Court wouldn't touch the case.
As another point along the same lines, I was all set to fly from Michigan to Rhode Island. My confirmation number was "4EBSOB". Looks harmless but being an old sailor myself, I saw the fun it it since I was flying "East-bound". I showed a lawyer friend because he's a drinking buddy and I thought it was funny. He insisted that he'd take it as a case and could win. :blink: I turned him down.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 5, 2013 at 4:13 am
Jeff Moden (3/4/2013)
Charmer (3/15/2012)
SQLKnowItAll (3/15/2012)
The first problem is that @DecNumber is a BIGINT. You cannot store characters in BIGINT. First you need to change this to CHAR(2) or something that applies. Does the sequence matter? Can it start at AZ and then go to YD? Or are you eventually going to order this?ok i will change the data type...sequence is not important....maintaining with unique 2 digit values or 3 digit depends upon the need...it must fit for some large row counts(maximum 2000)...
DO NOT USE RANDOM 3 CHARACTER "words". The people that get the swear words have the right to sue you.
Sue who? Developer? :w00t:
Actually, I have an indemnity insurance for these sort of situations...;-)
March 5, 2013 at 10:15 am
Nah... Developers got no money. They go after the company itself.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply