Prepending fields

[drkestrel]

I have a field within a DB table with which leading fields need to be pre-fixed with 000000. i.e.
with

field1  char(10)

If I want to insert 19 into field 1, it should become 0000000019

If I want to update field 1 to become 9878, it should be come 0000009878.

I heard in SQL Server, there is an option to define the field type to do this. Is this the case for Oracle or not?

If not, would Trigger be the next most efficient/easiest approach?

 

[Guest_imported]

You could use a format mask, e.g. to_char(to_number(field1),'0000000000')) - either when you wish to display the field, or within a trigger.

By the way, Oracle recommends that VARCHAR2 is used instead of CHAR.

 

[drkestrel]

don't understand....

is to_char a trigger method?
how could I build the intelligence so that if My give it 3 9s, it would only prepend 7 0s, and if I give it 5 9s, it would prepend 5 0s, etc. etc.

I presume Varchar is the same as varchar2?? But for efficiency purpose, would there be any advantage of doing it if all fields are prepended(and stored) as 10 digits? I guess the Var bit is because the string are of different length?

 

[MikeLacey]

In theory if you're storing fixed length strings there's no advantage in using VARCHAR2 to store them. I practise if you always use VARCHAR2 for string data you will never be surprised by getting a string contain blanks when you were expecting a NULL string, or vice versa. Even with single character columns we always use VARCHAR2. Mike
michael.j.lacey@ntlworld.com
Email welcome if you're in a hurry or something -- but post in tek-tips as well please, and I will post my reply here as well.

 

[irvina]

You can also use LPAD(your_value, 10, '0').

LPAD is an Oracle built in function and is used to Left PAD your_value to a length of 10 using '0' as its filler.

So, LPAD(19, 10, '0') will give you 0000000019.

You can use LPAD in any of your insert (or other) statements.