Not logged in - Login

Increment number portion of a text / number string

The following is an example of how to increment the number portion of a string when the string has a non-numeric prefix- i.e. 'ABC001234'

declare @sInputNum varchar(100)

set @sInputNum = 'ABC003434'
print @sInputNum


-- trim any leading / training spaces
set @sInputNum = ltrim(rtrim(@sInputNum))

-- declare some work variables
declare @lInputLen int
       , @lPrefixLen int
       , @lNumberPart int
       , @sPrefix varchar(100)
       , @temp int
                     
-- declare our output variable
declare @sOutput varchar(100)


-- get the input length
set @lInputLen = len(@sInputNum)

-- initialize our temp variable to be the input len
set @temp = @lInputLen

-- get the length of the string prefix
WHILE (@temp > 0 and isnumeric(substring(@sInputNum, @temp, 100)) = 1)
begin
       -- keep decrementing our temp variable while we're stil in the number part
       set @temp = @temp - 1;
end

-- get the prefix length
if (@temp > 0)
begin
       set @lPrefixLen = @temp
end else begin
       set @lPrefixLen = 0
end

-- get the character prefix
set @sPrefix = substring(@sInputNum, 1, @lPrefixLen)
-- get the number part
set @lNumberPart = convert(int, substring(@sInputNum, @lPrefixLen + 1, 100))
-- increment the number part
set @lNumberPart = @lNumberPart + 1

-- initialize our output variable with the number part
set @sOutput = ltrim(rtrim(convert(varchar, @lNumberPart)))
-- calculate how many '0' characters we need to add in, if any
set @temp = @lInputLen - @lPrefixLen - len(@sOutput)
if (@temp > 0)
begin
       -- prepend the '0' characters
       set @sOutput = replicate('0', @temp) + @sOutput
end

-- add the prefix
set @sOutput = @sPrefix + @sOutput


SELECT NewNum = @sOutput