Alphanumeric increament [0-9] and [A-Z]

[baran121]

Hi ,
i wanted to increase value of my max Alphanumeric field.

http://www.tek-tips.com/viewthread.cfm?qid=1232783

i searched here i found in link that Select Char(1 + Ascii('A'))

so i use it in my table.
select top 1 Char(1 + Ascii( left(opr_sec,1))) from model_oper where model_num=32 and opr_sec is not null group by left(opr_sec,1) order by left(opr_sec,1) desc

i have a question is it possible to increase just between [0-9] and [A-Z] . i dont wanna use :,Ñ,.etc
thank you.

 

[Olaf Doschke]

How about asking your local ice-cream parlor?

Well, since 0-9 have ascii codes 0x30-0x39 and letters begin either at 0x41 (A) or 0x61 (a) there is a gap.
You have to skip 0x3A to 0x40 or even to 0x60 and the simplest way to do so would be computing Char(Ascii(x)+1) and if that is the colon ) you replace that with A and then continue up to Z.

Select 
   replace(char(ascii('0')+1),':','A'),
   replace(char(ascii('8')+1),':','A'), 
   replace(char(ascii('9')+1),':','A'), 
   replace(char(ascii('Y')+1),':','A')

The gap is now simply skipped, as arriving at : you replace that by A. The replace is doing nothing in all other cases.

You can also specify any characters and simply determine the current position and take its neighbor, as in

Select 
   Substring('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',charindex('0','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',1)+1,1),
   Substring('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',charindex('8','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',1)+1,1),
   Substring('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',charindex('9','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',1)+1,1),
   Substring('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',charindex('Y','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',1)+1,1)

This is longer, but you can choose any set of characters you like, eg you could continue with a-z and have 10+26+26=62 different digits.

Bye, Olaf.

PS results will have 4 fields with 1,9,A,and Z as values, the follow up digits of 0,8,9,and Y.

 

[Olaf Doschke]

By the way: adding the 0 as last character in the set '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0' the thing would never error and turn a Z back to a 0. Having a 0 then would mean to increment the next higher digit, if you ever get there.

Bye, Olaf.

 

[baran121]

thank you