Morpheus7
asked on
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
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
Éric Moreau
would be much easier if you split your field into 2 fields: ABC in the first and the numeric value into the one.
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.
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.
ASKER
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
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
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)
DECLARE @x VARCHAR(20) = 'ABC567890'
SELECT @x, Left(@x, PatIndex('%[0-9]%', @x + '1') - 1), SUBSTRING(@x, PatIndex('%[0-9]%', @x + '1'), 10)
ASKER
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.
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.
I still don't understand!
ASKER
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
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
with what you have here, you can now split the code in 2, then find the max and increment as you would!
ASKER
What would be the best way to code this?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.