Alpha 
number	Job range	Base calculation	Jobs available
1	I000A-IZZZZ	34^4th power	        1,336,336
2	I00A0-ZZZ9	34^3rd power x 10	393,040
3	I0A00-IZZ99	34^2nd power x 100	115,600
4	IA000-IZ999	34 x 1000	        34,000

quote:

---

The reason they indicate 34 instead of 36 is because the documentation indicates that 'I' and 'T' are skipped. However, through testing I found that they aren't. The ERP company admitted the error in their documentation.

---

quote:

---

Originally posted by DavidChel

In my post, you'll notice why they used 34.

quote:

---

The reason they indicate 34 instead of 36 is because the documentation indicates that 'I' and 'T' are skipped. However, through testing I found that they aren't. The ERP company admitted the error in their documentation.

---

The number should actually be 36.

I don't think your solution applies Lamprey since there are 36 options per spot.

Anybody?

---

quote:

---

Job order number sequencing is based on a mathematical system known as base 36 numbering. In base 36:

Letters are inserted beginning in the right-most column (i.e., column 1) and moving to the left as the value is incremented.
This expands the available number of characters per column from 10 (0-9) to 36 (0-9 followed by A-Z).
It is then possible to generate many unique document numbers in sequence using a limited set of only four or five digits.

---

create function dbo.fn_base36(@mystr varchar(8000))
returns bigint
as
BEGIN
set @mystr = reverse(@mystr)
declare @len int, @onechar char(1), @pos int, @numvalue bigint
declare @arrystr varchar(100)
set @arrystr = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
select @len=len(@mystr)
set @onechar = ''
set @pos = 1
set @numvalue = 0
while @pos<=@len
	begin
		select @onechar = substring(@mystr,@pos,1)
		select @numvalue = (charindex(@onechar,@arrystr)-1)*power(len(@arrystr),@pos-1)+@numvalue
		set @pos = @pos + 1
	end

return @numvalue
END

quote:

---

Job order number sequencing is based on a mathematical system known as base 36 numbering. In base 36:

---

select (dbo.fn_base36('ZZZ')-dbo.fn_base36('3CA'))*10+8

select (dbo.fn_base36('ZZZ')*10+9) - (dbo.fn_base36('3CA')*10+1)

select dbo.fn_base36('ZZZ')*10 + 9 - (dbo.fn_base36('3CA')*10 - 1 

select dbo.fn_base36('ZZZ')*10 - (dbo.fn_base36('3CA')*10 + 9 - 1 
..
select (dbo.fn_base36('ZZZ')-dbo.fn_base36('3CA'))*10+8

select (dbo.fn_base36('ZZ') - dbo.fn_base36('C5') ) * power(10,2) + (99-49)

quote:

---

Originally posted by Peso

Beware that input parameter of 8000 chars of Z on base 36, needs 12451 characters in base 10.
Way over the BIGINT datatype.

A full BIGINT in base 10 is only 13 characters in base 36 using Z.

---

E 12°55'05.63"
N 56°04'39.26"

---

Declare @mystr varchar(8000)

set @mystr = 'C549'          

set @mystr = reverse(@mystr)

declare @len int, @onechar char(1), @pos int, @numvalue bigint
declare @arrystr varchar(100)
set @arrystr = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
select @len=len(@mystr)
set @onechar = ''
set @pos = 1
set @numvalue = 0
while @pos<=@len
	begin
		select @onechar = substring(@mystr,@pos,1)
		select @numvalue = (charindex(@onechar,@arrystr)-1)*power(len(@arrystr),@pos-1)+@numvalue
		set @pos = @pos + 1
	end

select @numvalue

DECLARE @mystr varchar(8000)

SET @mystr = (SELECT S.fcnumber FROM m2mdata02.dbo.sysequ S WHERE fcprompt = 'Next Internal J.O. Number')

SET @mystr = reverse(@mystr)

DECLARE @len int, @onechar char(1), @pos int, @numvalue bigint

DECLARE @arrystr varchar(100)

SET @arrystr = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'

SELECT @len=len(@mystr)

SET @onechar = ''

SET @pos = 1

SET @numvalue = 0

WHILE @pos<=@len
 BEGIN
  SELECT @onechar = substring(@mystr,@pos,1)
  SELECT @numvalue = (charindex(@onechar,@arrystr)-1)* power(len(@arrystr),@pos-1)+@numvalue
  SET @pos = @pos + 1
 END

SELECT @numvalue