0

How do I insert values with leading zeros in it?

Example.

db2 "insert into db2inst1.asd_currency 
(currency_code, currency_name, currency_decimal_count, currency_code_desc) 
values ('007', 'afghani', 2, 'afa'), ('009','lek', 2, 'all')"

Its output would give me something like below

db2 "select * from fcm_currency"

CURRENCY_CODE CURRENCY_NAME        CURRENCY_DECIMAL_COUNT CURRENCY_CODE_DESC
------------- -------------------- ---------------------- ------------------
            4 afghani                                   2 afa               
            8 lek                                       2 all               
            7 afghani                                   2 afa               
            9 lek                                       2 all               

  4 record(s) selected.

However, I need to retain the leading zeros because it's a code and I'll be needing that for my application.

EXPECTED OUTPUT:

CURRENCY_CODE CURRENCY_NAME        CURRENCY_DECIMAL_COUNT CURRENCY_CODE_DESC
------------- -------------------- ---------------------- ------------------
          004 afghani                                   2 afa               
          008 lek                                       2 all               
          007 afghani                                   2 afa               
          009 lek                                       2 all               

  4 record(s) selected.
  • 2
    If you want to retain formatting, you will need to define the column CURRENCY_CODE as character, not numeric. Leading zeroes have no significance in a number so they are always ignored. – mustaccio Jun 27 '19 at 11:03
  • In other words, you are inserting them just fine, they just are not staying because the column format does not allow them to exist. – James Jenkins Jun 27 '19 at 11:51
  • Thanks mustaccio. It worked! – Angela Marielle Estigoy Jun 28 '19 at 1:39
2

As is suggested in the comments, you should change the data type to, for example, char(3). If that is not possible, you can transform the value of the column like:

SELECT SUBSTR(DIGITS(CURRENCY_CODE),1,3) AS CURRENCY_CODE
FROM ...

or perhaps:

SELECT DIGITS(CAST(CURRENCY_CODE AS DECIMAL(3,0))) AS CURRENCY_CODE
FROM ...

which would be the same as:

SELECT DIGITS(DECIMAL(CURRENCY_CODE, 3,0)) AS CURRENCY_CODE
FROM ...

when you retrieve it

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.