Increment a varchar in SQL

Question

Basically, I want to increment a varchar in SQL the has a value of "ABC001".

I have code that adds one to an int, but I don't know how to get it working for a varchar:

SELECT
  NXT_NO
FROM
  TABLE

UPDATE
  TABLE
SET
  NXT_NO = NXT_NO + 1

Is there an easy way to increment if NXT_NO is a varchar?

I want:

ABC001 ABC002 ABC003

AND

It also needs to work with:

001, A0001, AB00001

Answer 1

Well, you can do something like this:

update table
    set nxt_no = left(next_no, 3) +
                  right('0000000' + cast(substring(next_no, 4, 100)+1 as varchar(255)), 4)

A bit brute force in my opinion.

By the way, you could use an identity column to autoincrement ids. If you then want to put a fixed prefix in front, you ca use a calculated column. Or take Bohemian's advice and store the prefix and number in different columns.

Answer 2

update 
    [table] 
set [nxt_no] = case when PATINDEX('%[0-9]%', [nxt_no]) > 0 then 
          left([nxt_no], PATINDEX('%[0-9]%', [nxt_no])-1) -- Text part
          + -- concat
          right( REPLICATE('0', LEN([nxt_no]) - PATINDEX('%[0-9]%', [nxt_no])+1) + convert( varchar, convert(int, right([nxt_no], LEN([nxt_no]) - PATINDEX('%[0-9]%', [nxt_no])+1))+1), LEN([nxt_no]) - PATINDEX('%[0-9]%', [nxt_no])+1)
else 
    [nxt_no] end