View original (French)

ORACLE - "Zero" as a prefix in a table

Solved
sanave Posted messages 3 Status Member -  
DROE Posted messages 148 Registration date   Status Member Last intervention   -
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

Answer 1 / 5
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
Answer 2 / 5
varfendell Posted messages 3259 Registration date   Status Member Last intervention   707
 
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
Answer 3 / 5
sanave Posted messages 3 Status Member
 
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
varfendell Posted messages 3259 Registration date   Status Member Last intervention   707
 
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
Answer 4 / 5
sanave Posted messages 3 Status Member
 
That's what I feared....!!

Well, in any case, thank you for your help,
I just have to get to work now...
0
Answer 5 / 5
DROE Posted messages 148 Registration date   Status Member Last intervention   48
 
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