CODEWITHSUNDEEP
mysqlsql
codmakr
codmakr

Reputation: 1

How to UPDATE a String with a int (+1) and convert back to String

I have the following problem.

An ID is stored in a field this field is declared as char(3).

I would like to rewrite this field and always count high by one from 001. and save this value back to char(3). The leading zeros must be back on.

In addition, the value of the previous row must be determined.

It is a foreign software so I have to live with the realities.

Can someone help me here?

Old Value 
char(3)
001 

New Value
char(3)
001+1 = 002

UPDATE example SET id = id + 1 WHERE  // Doenst work cause it is a string

Upvotes: 0

Views: 224

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270301

You can use lpad():

UPDATE example
    SET id = LPAD(id + 1, 3, '0')
    WHERE . . .;

However, if your id is a number, you should probably store it as a number. In fact, I would recommend an auto-increment column, if that fits your needs.

Then you can use a view or generated column to return it as a string.

Upvotes: 1

Related Questions