I have a column called deptno VARCHAR2(10)
it has all the records of length varying from 1 to 5
I need to display last two digits only. If the record is only one digit,
it should get lpadded with one zero to make it two digits.

For e.g.

Deptno
---------
1
450
23
5587
56540
5

should be displayed as

Deptno
-----------
01
50
23
87
40
05

Can any one help in this?? Do I need an IF like statement in SELECT ?? I dont really know how to begin with !

Thanks much

>Do I need an IF like statement in SELECT ??
"IF" is not a valid SQL construct.
It is past time for you to RTFM
http://download-west.oracle.com/docs...ns2a.htm#80856

---------------------

Thanks for the link. I am sure there is a way to do it.
Would get back to you, once i figure that out. KNOWLEDGE IS TO SHARE !

Start small/simple.
How do you extract the rightmost two digits/characters from a longer value?

Alternatively, you could zero fill on the left side & make all the values the same length. This would simplify the complete solution.

Hth
Bill

------------------------

AWESOME !!!

YOU MADE IT SO SIMPLE...thank you so much !!!

I was trying something like this...

SELECT
DECODE
(XRNDEPT,
LENGTH(TO_CHAR(XRNDEPT))>2,SUBSTR(XRNDEPT,
LENGTH(TO_CHAR(XRNDEPT))-1,LENGTH(TO_CHAR(XRNDEPT))),
LENGTH(TO_CHAR(XRNDEPT))<2,LPAD(XRNDEPT,2,'0'),
LENGTH(TO_CHAR(XRNDEPT))=2,XRNDEPT)
FROM CNVXREF;

------------------

THANKS MAN !!!

SELECT SUBSTR(LPAD(XRNDEPT,3,'0'),2,3)
FROM CNVXREF;

IT WORKED !!!