Solved

# Incrementation

Posted on 2014-08-05
133 Views
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
Question by:Morpheus7

LVL 69

Expert Comment

would be much easier if you split your field into 2 fields: ABC in the first and the numeric value into the one.
0

LVL 32

Expert Comment

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

Author Comment

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

LVL 69

Expert Comment

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

Author Comment

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

LVL 69

Expert Comment

I still don't understand!
0

Author Comment

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

LVL 69

Expert Comment

with what you have here, you can now split the code in 2, then find the max and increment as you would!
0

Author Comment

What would be the best way to code this?
0

LVL 69

Accepted Solution

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.