CODEWITHSUNDEEP
sqloracle-database
Burferd
Burferd

Reputation: 2491

How to increase non-numeric column value?

I have a column that is used for a sequence number. The values in the column look like 'WT0000004568'

I need to find the maximum value and increment the counter part by 1 to create a new sequence number. For this example, the resultant value would be 'WT0000004569'.

How do I do that?

Upvotes: 2

Views: 352

Answers (1)

Justin Cave
Justin Cave

Reputation: 231651

As Thilio pointed out, this is generally a bad design both from a performance and from a concurrency issue. If we assume that you have a single user system and aren't particularly concerned about performance, you could do something like

SQL> ed
Wrote file afiedt.buf

  1  select 'WT' ||
  2         to_char(
  3           to_number(substr('WT0000004568',3)) + 1,
  4           'fm0000000000')
  5*   from dual
SQL> /

'WT'||TO_CHAR
-------------
WT0000004569

Upvotes: 5

Related Questions