Incrementation

Hi,
I need to process data that will consist of an alpha numeric, for example ABC567890. The last 4 digits are the seed for additional rows. Additional data will consists of an integer, for example 20.
In the example above, I need to be able to create an additional 19 rows in the table, with the alpha numeric following on from the original data, so ABC567891, ABC567892, ABC567893, etc.
Any help would be appreciated.
Many thanks
Morpheus7Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Éric MoreauSenior .Net ConsultantCommented:
would be much easier if you split your field into 2 fields: ABC in the first and the numeric value into the one.
0
ste5anSenior DeveloperCommented:
It depends on the exact definition of your alpha-numeric value and the concrete process.

I would store that value in one column and an additional sequence number in a second column. Cause your describtion allows that a second process may start with ABC567900. When using the alpha numberic values as key, this would result in a key violation. Storing it this way, also allows us to tell afterwars the original alpha numeric number. Which means we can identify the process which created it.
0
Morpheus7Author Commented:
Hi,
Many thanks for the replies.

The plan is to receive the alpha numeric and the integer and store them in separate columns in the table. We would then create the additional rows in another table.

Thanks
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Éric MoreauSenior .Net ConsultantCommented:
so you want to know how to split it in 2 parts?

DECLARE @x VARCHAR(20) = 'ABC567890'
SELECT @x, Left(@x, PatIndex('%[0-9]%', @x + '1') - 1), SUBSTRING(@x, PatIndex('%[0-9]%', @x + '1'), 10)
0
Morpheus7Author Commented:
Hi,
Apologies, I don't think I explained myself.

When we receive the alpha numeric, ABC567890 and then the integer, 20, we would then increment the last 4 digits by one each time and then store them as ABC567891, ABC567892, ABC567893, etc, in another table.
0
Éric MoreauSenior .Net ConsultantCommented:
I still don't understand!
0
Morpheus7Author Commented:
The alpha numeric represents the starting number of a range of serial numbers for a product. The integer is the quantity of the product sold.
So to have a unique row for each of the individual items, we need to increment the last four digits of the serial number by one each time we insert into the orders table. So we end up, in this example, with a total of twenty rows for this product
0
Éric MoreauSenior .Net ConsultantCommented:
with what you have here, you can now split the code in 2, then find the max and increment as you would!
0
Morpheus7Author Commented:
What would be the best way to code this?
0
Éric MoreauSenior .Net ConsultantCommented:
you can surely find the max (Select MAX(codefield) from ...), then split (with the code I have provided already) and then do + 1 on it
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.