View original (French)

ORACLE - "Zero" as a prefix in a table

Solved
sanave -  
Bonjour,

I currently have a partially filled numeric table:

1- How is it possible to add "zero" in front of the existing number, knowing that it should be limited to 4 characters?
Examples:
For 23, I would like 0023;
for 125, I would like 0125.

2 - When creating the table, what settings need to be made to achieve this result?

Thank you for your help...

5 answers

  1. jykoe
    It is possible to prefix the representation of a number with '0's as follows:
    let's assume we have a COMMON table with a CODE_INSEE field of type number:

    LPAD(cast(CODE_INSEE as varchar2(5 BYTE)),5,'0')

    will return '01034' for the municipality with an INSEE code of 1034 (Belley, in Ain)

    For INSEE codes that fit within 5 characters, this function will not add any 0s.
    2
    1. varfendell
      Hello,

      Have you tried filling in the int field with 4 afterwards (to indicate that the int must contain 4 digits)?

      Otherwise, change the type to a varchar4
      --
      Brittany...the most beautiful place in the world.
      0
      1. sanave Author
        Here is what I currently have,

        Name: COPOLE
        Data type: NUMBER
        Size: 4

        If I change NUMBER to VARCHAR, it tells me:

        "ORA-01439: a column must be empty to change its data type".

        So, obviously I cannot change the settings midway!!

        (For Brittany, I completely agree!!!)
        0
        1. varfendell
          In this case, if your table is not too large and not too filled, you can recreate it and refill it, but with the correct type for the column that interests you.
          0
      2. sanave Author
        That's what I feared....!!

        Well, in any case, thank you for your help,
        I just have to get to work now...
        0
        2. DROE
          Hello,

          a solution among others:

          should this value be stored in the database in this format?
          If not then only for display:

          SELECT TO_CHAR(COPOLE,'0000') FROM dual; ----> 0125

          Indeed, you cannot change the type of a column if it is populated.

          Solution

          1- Rename the column COPOLE to A_SUPP
          Delete the index if the column is indexed.

          ALTER TABLE table_name RENAME COLUMN copole TO a_supp;

          2- create a new column COPOLE of type VARCHAR2

          ALTER TABLE table_name ADD (copole VARCHAR2(4));

          3- update this column with transformation

          UPDATE table_name set COPOLE=TO_CHAR(a_supp,'0000')

          4 - Delete the column A_SUPP

          5- Reindex the column COPOLE

          DROE

          http://www.dba-ora.fr/article-function-sql-oracle-to_char-70874668.html
          --
          Experience is the name everyone gives to their mistakes.
          0