1

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

Ian Best
  • 510
  • 1
  • 11
  • 23
  • 1
    Your life would be easier if you had two columns; one for the ABC and one for the number part. Joining them is a rendering issue, but you've buried it into the database. That is a mistake. – Bohemian Feb 19 '13 at 20:37
  • The problem is it's not my table. I am simply pulling the data from it, but whenever I get NXT_NO, I have to add one to it. Otherwise, the table will get unorganized and over time cause many problems. – Ian Best Feb 19 '13 at 20:38

2 Answers2

3

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I appreciate the response. The problem is that I never know what the characters in NXT_NO are. I just know that their are possibly some letters at the beginning. For example, NXT_NO could be in the format '1001', 'A1001', 'ABC1001'. I need something that can handle all cases. – Ian Best Feb 19 '13 at 20:45
1
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
Retired_User
  • 1,595
  • 11
  • 17
  • I appreciate the response. NXT_NO could be in the format '1001', 'A1001', 'ABC1001'. I need something that can handle all cases. – Ian Best Feb 19 '13 at 20:50
  • by the way, if you want to use numbers with 4 characters, you have to replace all 3's by 4 and "000" by "0000" – Retired_User Feb 19 '13 at 20:53
  • This is all very helpful, thank you. But, and I promise this is the last thing, it needs to work for a variable amount of numbers, also. 'A100003', 'ABC12' – Ian Best Feb 19 '13 at 20:53