Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2004
    Posts
    22

    Exclamation Unanswered: SQL Built in FUNCTIONS

    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

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >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
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2004
    Posts
    22

    Thumbs up

    Quote Originally Posted by anacedent
    >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 !

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Start small/simple.
    How do you extract the rightmost two digits/characters from a longer value?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Alternatively, you could zero fill on the left side & make all the values the same length. This would simplify the complete solution.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Code:
    select substr('0'||deptno,-2)
    from yourtable
    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  7. #7
    Join Date
    Apr 2004
    Posts
    22

    Talking

    Quote Originally Posted by anacedent
    Alternatively, you could zero fill on the left side & make all the values the same length. This would simplify the complete solution.
    ------------------------

    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;

  8. #8
    Join Date
    Apr 2004
    Posts
    22

    Talking

    Quote Originally Posted by billm
    Code:
    select substr('0'||deptno,-2)
    from yourtable
    Hth
    Bill
    ------------------

    THANKS MAN !!!

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

    IT WORKED !!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •