Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – MariaDB field + 1

How do I increase a field by +1 that was previously 0001 and then should be 0002?
I am currently using the following command:

SELECT fieldA+1
FROM table
WHERE ID = 1234;

But this only turns 0001 into 2

Its a very basic question but i dont get it

Tags:

2

Answers


  1. 0

    A field of type INT must first be converted to a string (or CHAR) in order to perform string-based operations, such as a pad.

    For example:

    SELECT 
    LPAD(CAST(fieldA + 1 as CHAR), 4, '0')
FROM table
WHERE ID = 1234;

    Where the LPAD parameters are as follows: LPAD(str, len [,padstr]). If the field is already of CHAR or VARCHAR type, the CAST() function is not required.

    Related documentation:

    Aside:

    • It might be a consideration to leave ‘formatting’ activities to the front end, and leave the ‘data’ activities to the back end (i.e. database). Whichever approach you decide to take, be consistent. If/when this is updated, it can be frustrating to other developers and maintainers to not know exactly where to look when applying an update. (e.g. "Does this change apply to the database, or to the application?" is a question far too often asked.)
    Login or Signup to reply.
  2. 0

    How do I increase a field by +1 that was previously 0001 and then should be 0002?

    Use ZEROFILL column attribute accompanied by definite column length.

    CREATE TABLE test ( id SMALLINT(4) ZEROFILL );
INSERT INTO test VALUES (1);
SELECT * FROM test;
    id
    0001 
    UPDATE test SET id = id + 1;
SELECT * FROM test;
    id
    0002

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search