String Functions: Incrementing a Number in a Char

By Bill Graziano on 27 November 2000 | Tags: Queries , Functions

Derrick writes "I have a value : 'PIT000' I need to increment this value i.e. PIT001, PIT002, etc until PIT999. How do I do this using a SQL query. The next step is: once you reach PIT999, the value needs to change to PIU000!!" We certainly can do this in SQL Server and it gives us a chance to talk about some neat string functions too.

I probably shouldn't even ask this question . . . but "Which Pointy Haired Boss thought this numbering scheme up?" Actually I used to work for a consulting company that used this exact approach to number projects. And in spite of all my complaints it worked pretty well. Except I think they had a person generate the numbers. Oh well. This actually is pretty easy to do in SQL Server and along the way we'll learn some string functions.

The first step is to pull out the numeric portion of your "Value" (I really don't know what else to call it so I'll use "Value"). I'll use a snippet of code that looks like this:

DECLARE @iNumValue int, @charOldValue char(6) SELECT @iNumValue = CAST( SUBSTRING(ValueColumnName, 4, 3) AS Integer ), @charOldValue = ValueColumnName

First, I declared a variable to hold the integer part of this Value. I also captured the full Value in a variable which we'll use below. Then I used the substring function in SQL Server. SUBSTRING has the following format:

SUBSTRING(expression, start, length)

Substring in SQL Server works like substring in practically every other language. You pass it a string, a starting position and how many characters you want and it returns a substring. I'm hoping that your Values are always six characters with the right three being numeric.

I used the CAST function to convert this string to an integer. The syntax of the CAST function looks like this:

CAST(expression AS data_type)

CAST takes an expression and converts to the target datatype which is integer in our case. You could also use the CONVERT function. CAST is an ANSI SQL-92 standard so I try to use it everywhere except date conversions. CONVERT does a much better job on those.

Incrementing the numeric part of your Value is easy:

SET @iNumValue = @iNumValue + 1

I'll handle the rollover past 1000 below. Now I have to put the numeric part back together with the alphanumeric part. Here's that code:

DECLARE @charNewValue char(6) SET @charNewValue = LEFT(@charOldValue, 3) + RIGHT ('000' + CAST(@iNumValue AS varchar(3), 3 )

The LEFT and RIGHT functions do just what they do in every other language. We're starting with the three left characters of our original string and appending the numeric portion on the right. Our CAST function converts the numeric value back to a VARCHAR value. I use VARCHAR because CHAR appends spaces to the end of the string. I concatenate this with three zeroes (as a string) and take the right three characters.

So if I started with PIT002 the incremented numeric portion converted back to a VARCHAR is '3'. I then take the RIGHT three characters of ('000' + '3') and get '003' which is just what I wanted. Now let's handle incrementing a character. You might write code something like this: SET @iNumValue = @iNumValue + 1 IF @iNumValue > 999 BEGIN Set iNumValue = 0 -- Do the stuff here to increment the alpha part(see below) END -- Then put the string back together (see above)
Incrementing a string is a little trickier than incrementing a number. You have to convert the character to ASCII, increment the value and then convert it back to a character. Here's a piece of code to increment a single character: DECLARE @chOneLetter char(1) Set @chOneLetter = 'A' SELECT @chOneLetter = CHAR(ASCII(@chOneLetter) + 1) This little snippet will return a 'B' in this case. The ASCII function returns the numeric ASCII value of our variable. We add one to this value. The CHAR function converts an ASCII value into it's equivilent character. Of course you also have to handle the case of incrementing past Z but I'll leave that to you. If you have SQL Server 2000 you might consider writing these as a number of User Defined Functions. You could write a function that would return the next Value when passed a specific value. Could you do this easier on the client in VBScript, Java, VB, etc.? Maybe but you would have more trouble doing it inside a transaction to insert new values. Hope this help and enjoy those string functions :) -graz