Alphanumeric sequence

[johann59]

I'm trying to create an alphanumeric sequence. I have a single table with 200,000 records. I want to add a alphanumeric sequence consisting of no more than 3 alpha characters and 4 numeric characters.

I've searched the forum to see if I could find something in that regards but I couldn't.

Example on a field called seq1:

1A1A1A1
1A1A1A2
1A1A1A3, etc

I've tried using sys(2015) but it gives me too many alpha characters. My client insists on only 3 alpha characters and a string no longer than 7 characters. The order of the characters doesn't matter. The 3 alphas could be in the middle of the string. The starting order doesn't matter either. The sequence can start with a 1 or 2, etc.

This is what I found in the forums:
REPLACE seq1 WITH SUBSTR(SYS(2015),1) ALL

Are there any commands that would create such a sequence or if someone could give some pointers. I'm using VFP 9.

Any help is highly appreciated.

 

[MrDataGuy]

OK here is my brute strength klugey solution: The 'key' is to use hex values.
(See

http://support.microsoft.com/kb/155389

)

C = 0
FOR I = I TO 200000
	? I 
	C = c + 1
	?? [ ] 
	
	Possible_Solution = RIGHT(TRANSFORM( C,"@0"), 7)
	DO WHILE NOT Meets_Solution( Possible_Solution )
		C = c + 1
		Possible_Solution = RIGHT(TRANSFORM(C,"@0"), 7)
	EndDo
	?? Possible_Solution

ENDFOR

FUNCTION Meets_Solution( pPossible_Solution )
	Numbers = 0
	
	Numbers = Numbers + IIF( SUBSTR( pPossible_Solution, 1, 1 ) $ [1234567890], 1, 0 )
	Numbers = Numbers + IIF( SUBSTR( pPossible_Solution, 2, 1 ) $ [1234567890], 1, 0 )
	Numbers = Numbers + IIF( SUBSTR( pPossible_Solution, 3, 1 ) $ [1234567890], 1, 0 )
	Numbers = Numbers + IIF( SUBSTR( pPossible_Solution, 4, 1 ) $ [1234567890], 1, 0 )
	Numbers = Numbers + IIF( SUBSTR( pPossible_Solution, 5, 1 ) $ [1234567890], 1, 0 )
	Numbers = Numbers + IIF( SUBSTR( pPossible_Solution, 6, 1 ) $ [1234567890], 1, 0 )
	Numbers = Numbers + IIF( SUBSTR( pPossible_Solution, 7, 1 ) $ [1234567890], 1, 0 )
	
	RETURN ( Numbers = 4 )

ENDFUNC

Lion Crest Software Services
Anthony L. Testi
President

http://www.LionCrestSoftwareServices.com

 

[Dan Freeman]

Why? (I'm really curious.)

 

[Olaf Doschke]

I second dan,

Is it a kind of product key? Why the limit to 3 alphas?

What you could do is seperate this into a c(3) and an int with a normal sequence, limited up to 9999. Then only a carry to 10000 would cause a carry in the letters.

Bye, Olaf.

 

[Olaf Doschke]

To stay with the number of alpha and numeric digits you could seperate the sequences to letters only and digits only. eg Z would not turn to 0 in the next count, but to A, 9 would not turn to A, but 0.

Also 9Z9Z9Z9 would turn to 0A0A0A0 and stay with it's length. Something you might never experience, if starting with 1A1A1A1.

Nevertheless an integer sequence always is sufficient in vfp, as you have 1GB records at max, each minimum 2 bytes long (1 for deletion flag, 1 for eg a bool or N(1) or C(1)) and thus don't need more than 1GB primary keys. Also int is 4 Byte only.

Despite of that here's a sequence generator function:

lcCounter='9Z9Z9Z5'
For lnCounts=1 to 30
   lcCounter = Sequence(lcCounter)
   ?lcCounter
EndFor

Function Sequence(tcPrevious)
   #Define ccInitial "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"
   #Define ccFinal   "1234567890BCDEFGHIJKLMNOPQRSTUVWXYZA"

   Local lcLeft, lcRight, lcDigit, lcNext

   lcLeft  = tcPrevious
   lcRight = ''
   Do While Len(lcLeft)>0
      lcDigit = Chrtran(Right(lcLeft,1),ccInitial,ccFinal)
      lcLeft  = Left(lcLeft,Len(lcLeft)-1)
      lcRight = lcDigit+lcRight
      If lcDigit # 'A' AND lcDigit # '0' && no carry
         lcNext = lcLeft+lcRight
         Exit
      Endif
   EndDo
   
   Return Evl(lcNext,lcRight)
Endfunc

 

[johann59]

Thank you VERY much to MrDataGuy & Olaf. I was able to do exactly what our client needed. This forum rocks!

I'm guessing the reason of why they wanted this kind of sequence is for a mailing they're doing. I assume some type of ID. We are embedding this number in a QR barcode.

Again, thanks.