Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    9

    Unanswered: Padding with 0's to obtain correct length?

    I have a column I wish to select called event_hour. Its of the format HHMM and I wish to group my results by the hour. i.e. substr(event_hour,1,2).

    However, the values vary from either 3 characters of 4 characters. The time is supposed to be coded by the 24hr clock but but its not quite like that.

    0900hrs will be '900'
    1000hrs will be '1000'
    2130hrs will be '2130
    0830hrs will be '830'

    So, by grouping using the substr I get messy results. Is it possible to pad out the data with a 0 on those short values so I can perform a correct substr?
    I thought of concatinating but that would lead me to have not only the correct 0830 for example, but also incorrect 02130.

    Im sure there must be a simple solution. But I'm no expert.

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Try something like:
    SELECT TO_CHAR(SYSDATE,'HH24MI') FROM DUAL;

    This should give you 4 characters each time

    HTH
    Gregg

  3. #3
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208
    If event_hours is a DATE use the to_char

    if event_hours is a character datatype ie VARCHAR2 use the LPAD function

    LPAD( string, length, charater to pad with)

    select LPAD(event_hours,4,0)
    from table
    ....

    HIH

  4. #4
    Join Date
    Mar 2004
    Posts
    9
    thanks for the speedy reply. I should have specified the data type, but carloa was correct. The lpad work a treat

Posting Permissions

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