Tags: complex, database, digits, example, mysql, number, oracle, padded, sequence, sql, write, zeros
I need to write a sequence that 1) always has 4 digits, the first sequence number would be a 1 with three zeros padded to the left, for example 0001, 0002, 0003-- 0345, 0346, 0347 and so on (it will never go over 9999). Also 2) The sequence must be reset to 0001 on Dec 31 every year.
Does anyone have know how to do this? Thanks in advance.
Leave a comment...
- 1 Comments
- Well, 0001 is technically not a number so I'm assuming you mean you want a string. Why can't you just use a normal sequence and format it as to_char(n, '0000') when you use it? As for reseting it on a specific date, that sounds like a task for a batch job. Either drop and recreate the sequence starting at one again or set the sequence to cycle at 9999 and nextval in a loop until it rolls over.
Richard#1; Thu, 21 Feb 2008 23:01:00 GMT