February 9, 2015 at 10:25 am
Jeff Moden (2/9/2015)
K2mission (2/9/2015)
Sean,The thought was to allow for as many 6-character combinations as possible. Using the alphabet along with numbers offered 2,176,782,336 combinations whereas numbers alone would only offer 1,000,000 (zero-base). However, in light of the many spellings that could be fabricated from this arrangement (thanks to all for the feedback and laughs), it's best that other solutions are utilized. Our team had restrictions in what was permitted to be sent to a vendor. We were able to work out another format that better represented the needs of each party.
Thanks,
Steve
At least remove the vowels and the letter "Y" from the mix so that you don't spell out so many offensive words in the process.
To be honest though, it only takes 4 bytes to get numbers from 0 to over two billion. Have a display limit of six bytes is a pretty silly requirement you've been given computationally speaking.
Pretty much have to remove V, C, and K too. There are just too many options even with no vowels where those three can get you into trouble.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 10, 2015 at 11:08 am
I ran into a situation where I had to generate odd "numbers" like this. Our system produces billing invoices and then has to post to the client back end system. Only 11 characters are available for the invoice "number". It is a string element in both systems. Rather than producing a list of all possibles and handing them out one by one I derive a scheme using the date and time expressed in modified base 36. The resulting system is collision free and roll over proof for at least the next hundred years or so.
If you don't want to get into CLR routines. There might be another way to consider. Incrementing the string is going to be one expensive function to run. What might be better is to write a function or stored procedure to encode an integer into the base 26 format shown. One could get the integer from a sequence if you are on SQL 2012 or later. Remember that 0 is A and 25 is Z. If the number is low then pad on the left with A's. My bet is the the "2005" was based on the year of some date.
Jeff you are right about the unfortunate words. Even with a subset of 4 letters you can go bad. Try 4 letter combinations where the first letter must be a K or W. KOOL is not so bad but there are others.
ATBCharles Kincaid
February 13, 2015 at 4:32 am
I was looking for a function like that a while ago to help me identify UK car registrations that were issued consecutively (our company buys vehicles in bulk and sometimes registers a bunch together). Here is my modification of the function posted earlier, handling upper and lower case, and numbers, with an extra parameter for determining whether numeric characters are issued 0,1,...,8,9 or 1,2,...9,0
CREATE FUNCTION [dbo].[fn_NextConsecutiveString] (
@String varchar(32) --would need to use some other kind of looping/recursion for strings longer than 32 characters, but this kind of function not likely to be needed for massive strings
,@NumberIssuedFirst tinyint --set to 0 or 1. If parameter set to 0 will consider 0 was the first number issued so will flip from 9 to 0 and continue recursion. If parameter set to 1 will flip from 0 to 1 and continue recursion, and will increment from 9 to 0 without further recursion. Probably not really relevant for UK car registrations , as the numbers relate to the semester rather than something that increments automatically for uniqueness. Presumably within each reg area (first two chars) and six month period (second two chars) they never need more unique values than AAA to ZZZ (last three chars), so it won't arise anyway.
)
RETURNS varchar(32)
AS
-- example usage:
-- SELECT dbo.fn_NextConsecutiveString('VX69EZZ',0) would return 'VX69FAA'
-- SELECT dbo.fn_NextConsecutiveString('VX69ZZZ',0) would return 'VX70AAA'
-- SELECT dbo.fn_NextConsecutiveString('VX99ZZZ',0) would return 'VY00AAA'
-- SELECT dbo.fn_NextConsecutiveString('VX99ZZZ',1) would return 'VX90AAA'
-- SELECT dbo.fn_NextConsecutiveString('vx90zZz',1) would return 'vx01aAa'
-- SELECT dbo.fn_NextConsecutiveString('VX10ZZZ',1) would return 'VX21AAA'
--this function is recursive, the depth of recursion will relate to how many characters it has to change
BEGIN
IF @NumberIssuedFirst NOT IN (0,1) RETURN cast('Error: When using function dbo.fn_NextConsecutiveString the second parameter @NumberIssuedFirst must be either 0 or 1' as int);
DECLARE @C smallint,@Return varchar(32), @Len smallint
SELECT @C=ASCII(RIGHT(@String,1))
SELECT @Len=LEN(@String)
SET @Return=CASE WHEN @C IN (90,122) --ends with 'Z' or 'z'
THEN CASE WHEN @Len=1
THEN CHAR(@C-25)
ELSE dbo.fn_NextConsecutiveString(LEFT(@String,LEN(@String)-1), @NumberIssuedFirst)+CHAR(@C-25) --changing last character to 'A' or 'a'
END
WHEN @NumberIssuedFirst=0 AND @C=57 --ends with 9 and 0 was issued first so we will flip from 9 to 0 (and continue recursion if more characters left)
THEN CASE WHEN @Len=1
THEN '0'
ELSE dbo.fn_NextConsecutiveString(LEFT(@String,LEN(@String)-1), @NumberIssuedFirst)+'0'
END
WHEN @NumberIssuedFirst=1 AND @C=48 --ends with 0 and 1 was issued first so we will flip from 0 to 1 (and continue recursion if more characters left)
THEN CASE WHEN @Len=1
THEN '1'
ELSE dbo.fn_NextConsecutiveString(LEFT(@String,LEN(@String)-1), @NumberIssuedFirst)+'1'
END
WHEN @NumberIssuedFirst=1 AND @C=57 --ends with 9 and 1 was issued first so we will increment from 9 to 0 (effectively acting like 10) without further recursion
THEN LEFT(@String,LEN(@String)-1)+'0'
ELSE --increment final character
LEFT(@String,LEN(@String)-1)+CHAR(@C+1)
END
RETURN @Return
END
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply