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

The ANSI standard includes a CASE structure, but I don't know if that's made it into Oracle yet...
Try this (T-SQL syntax - my Oracle is a little rusty) : Select
 (CASE
  WHEN ID < 9 THEN Convert( integer, '000' + string( ID ) )   WHEN ID between 10 and 99 THEN Convert( integer, '00' + string( ID ))   WHEN ID between 100 and 999 THEN Convert( integer, '0' + string( ID ))   ELSE ID
  END) as calc_ID
 From TABLE ;

If Oracle does not yet support CASE, then there is a method using characteristic function expressions, but for that I'll need my copy of Rozenshtein, which is in the office <G>.

Paul Horan
VCI
Springfield, MA

azhan98_at_tm.net.my wrote in message <6tsc3d$a9p$1_at_nnrp1.dejanews.com>...
>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
Received on Thu Sep 17 1998 - 23:22:14 CDT