Alpha Numeric number Generation

Question

Alpha Numeric number Generation

sqlenthu_11

SSC Eights!

Points: 800
More actions
March 25, 2013 at 12:49 pm

#275622

Hello,
Need help with in writing a Stored Procedure for Auto Alpha Numeric Generation. If I pass an Alpha numeric in the format mentioned below , I should get next number based on the following logic:
Format : It consists of 6 digit Alpha Numeric Code , where E is constant. For ex : E00001
SP should generate like this:
E00001 to E99999 when it reaches to E99999 then 9 on left will be replaced with "A"- EA0001
EA0001 - EA9999
EB0001 - EB9999
EZ0001 - EZ9999 when it reaches EZ9999 it should be incrmented to E0A001
Thanks,
Nick

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

nitin_456 (3/25/2013)
Hello,
Need help with in writing a Stored Procedure for Auto Alpha Numeric Generation. If I pass an Alpha numeric in the format mentioned below , I should get next number based on the following logic:
Format : It consists of 6 digit Alpha Numeric Code , where E is constant. For ex : E00001
SP should generate like this:
E00001 to E99999 when it reaches to E99999 then 9 on left will be replaced with "A"- EA0001
EA0001 - EA9999
EB0001 - EB9999
EZ0001 - EZ9999 when it reaches EZ9999 it should be incrmented to E0A001
Thanks,
Nick

This kind of things pops up around here from time to time. The short answer is don't do this. It seems like you have a 5 character code. What happens when you need a 6th position? If you are deadset on doing this you will have to do some awful calculations and ASCII math.

_______________________________________________________________

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/

sqlenthu_11 SSC Eights! Points: 800 More actions · Answer 2

sqlenthu_11

SSC Eights!

Points: 800

March 25, 2013 at 1:09 pm

#1600478

Hi Sean- It always be 5 digit Code + constant.

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

nitin_456 (3/25/2013)
Hi Sean- It always be 5 digit Code + constant.

The logic for this type of thing is horrible. It ends up being a scalar function and performance is not too good.

What have you tried so far?

_______________________________________________________________

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/

sqlenthu_11 SSC Eights! Points: 800 More actions · Answer 4

Totally Blank , Plz see if you can suggest something.

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

nitin_456 (3/25/2013)
Totally Blank , Plz see if you can suggest something.

Honestly I would suggest not doing this. The code will make your eyes bleed before you even get close to completion. The logic for this requires it to be scalar in nature. I have brute forced this type of thing in the past but seriously it is awful.

Maybe we can help find a different solution if we know more about the problem.

_______________________________________________________________

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/

sqlenthu_11 SSC Eights! Points: 800 More actions · Answer 6

Totally agree, but this is the task I got to do this.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

When EZ9999 incrments to E0A001, what happens as E0A999?

sqlenthu_11 SSC Eights! Points: 800 More actions · Answer 8

When EZ9999 incrments to E0A001, what happens as E0A999?

ans : When EZ9999 increments to E0A001

then afterwards It E0a999 will be increment to E0B001

Or What can be done is

After EZ9999 it can be incremented to EAA001

Thanks Guys for looking at the post !

Sean Lange SSC Guru Points: 286590 More actions · Answer 9

Can you use CLR? This type of heavy string manipulation is probably going to be a lot easier to work with in .NET instead of pure t-sql.

_______________________________________________________________

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/

David Webb-CDS SSCoach Points: 17398 More actions · Answer 10

When E0A001 incremenst to E0Z999, what comes next:

E1A001?

Does that go to EZZ999? The rules for creating the Aplha part don't seem very clear to me.

I'd agree with Sean that this is a REALLY BAD scheme for creating a unique value. Columns are supposed to have a data type and data types have operations defined on them. This column would have to be a character data type but have numeric operations used upon it. Hardly ever a good idea.

And then again, I might be wrong ...
David Webb

Lowell SSC Guru Points: 323518 More actions · Answer 11

i have some old examples in my snippets, but this just goes back to the same comments all other posters have made:

bad idea. the posters so far on here all have years more experience than you, tens of thousands of posts here on SSC volunteering, and all of them are telling you you it's bad.

your rules don't even follow a mathematical calculation, so you cannot do the typical identity() on the table + calculated column I've seen as solutions.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ScottPletcher SSC Guru Points: 101093 More actions · Answer 12

Your stated incrementation method is so unusual and customized that I'd suggest just creating table with pre-generated key values; and, when you need to increment by one, instead of actual "incrementing", lookup the next value in the keys table.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

sqlenthu_11 SSC Eights! Points: 800 More actions · Answer 13

So the idea here is to Generate 2 -3 million consecutive unique numbers by using 5 Alpha Numeric digit. Please if you can help around this will be great.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 14

Or convince your powers that be to change what they are asking you accomplish.