[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 146
  • Last Modified:

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
0
Morpheus7
Asked:
Morpheus7
  • 5
  • 4
1 Solution
 
É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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
É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

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now