I have a table which containd 10 column.
One of the column contains numbers.
Now I have to append "0" s infront of these no's for all rows in a table .
can some one help me out.
sample column data as below
If you want to pad the numbers so that they all contain, say, 10 digits (adding leading 0's if they are not already 10 digits), then
TO_CHAR (msidno, '0000000000')
If you want to add a leading 0, regardless of the number of digits:
'0' || TO_CHAR (msidno)
Either way, given that msidno is a NUMBER (an integer) the result will be a VARCHAR2.
I hope this answers your question.
If not, post a little sample data (CREATE TABLE and INSERT statements) , and the results you want from that data. Make sure the sample data shows all the special cases you might need to handle (for example, different numbers of digits).
Explain, using specific examples, how you get those results from that data.
Always say what version of Oracle you're using (e.g. 18.104.22.168.0).
See the forum FAQ: https://forums.oracle.com/message/9362002
Do you really think appending 0's to the numbers will be stored in the same way? how many times have you appended the numbers with 0 when writing a Cheque? If you have not, then why do you think should oracle do it?
Irrespective of whether you precede a number with a 0, if the column data type is Number/Numeric, it will be stripped off, because it is insignificant to store leading zeroes.
If you want to display the numbers with a leading Zero, then use the way Rahul has shown in previous post. Another way to do so is using TO_CHAR with Fm Format model.
see an example:
with data as
select 10000 col from dual union all
select 999 col from dual union all
select 9 col from dual
select col, to_char(col, 'fm000000') format_col
No that's not the correct answer. Do that and then query the table. You will see that nothing changed. The column is a number. It is stored as a number. Your update statement will not cause any change in how it's stored because the number 123 has the exact same value as the number 0123. You can't store the leading zero in the database. You have to add the leading zero when you display the number to a human which means any applications which access the data need to make the change.
I have not told clumn is of number type its a varchar2 type contains numbers as a values .. so as per Frankkulash suggestion the update statement works fine.
The fact that your column is not a number but a varchar2 would should have been stated up front, instead of making us guess.
The fact that your column is a varchar means it is not and never was a number. It is simply a string of numeric characters.
In that case you don't need th to_char. I thought to_char would give you an error when you gave it a varchar2 but evidently it doesn't.
Oracle kindly converts it to a number for you first, then it applies the to_char to that number.
It appears that it doesn't. Otherwise this would give me an error
SQL> select to_char('abc') from dual;