Re: SQL- Left justified with zero maintaining 4 digits of number.

Hi

try LTRIM(TO_CHAR(ID,'0009') )
The ltrim is needed to delete the extra space - space is default for ltrim -. This wil get yoy a field of exactly 4 digits. If the field can be negative you would lose the minus sign. But for barcodes this seems unlikely.

In article <6tsc3d$a9p$1_at_nnrp1.dejanews.com>,   azhan98_at_tm.net.my wrote:
> Hi all,
>
> I have problem again.
> I want to create a number with 4 digits.Using SQL or PL/SQL.
> The number will be in a sequential pattern (as an ID).
>
> The problem is, how can I format the number so that it can be always
> " left justified with 0 (Zero) but maintaining the 4 digit limits ".
>
> Here are the one I need:
> ---------------------------
> if the number is less than 0 - it will be 0001,0002,0003 .... (4 digits)
> if the number is more than 100
> but less than 1000 - it will be 0100,0101....0999.. (4 digits)
> if the number is more than 1000 - it will be 1000,...5000,.... (4 digits)
>
> and so on...
> I like to use a LOOP control structure but I cannot make a header
> of 0 because it will go over the 4 digits limit, say if 0 is a header
>
> for 1 it will be 01 (2 digits)
> for 10 it will be 010 (3 digits)
> for 100 it will be 0100 (4 digits)
> for 1000 it will be 01000 (5 digits)
>
> The reason why I "must maintain the 4 digits structure" is because this number
> will later on used as a bar code reference which have a format of
> 4 digits. Does anybody out there have an idea?Can it be done?
>
> Appreciate your help.Thanks.
> Norazman
>
> # FOR ALL WHO HAVE REPLIED TO ME BEFORE, THANK YOU VERY MUCH. I ALWAYS
> APPRECIATE THAT.
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Fri Sep 18 1998 - 07:16:13 CDT