We help IT Professionals succeed at work.
Private
Troubleshooting Question

ORALCE PL/SQL LPAD for Hour string

ASPDEV
ASPDEV asked
on
71 Views
Last Modified: 2020-08-19
Hello Experts.

I have a quick thing, in my PL/SQL query I have a VARCHAR2 field for Time which has Hour & Min like 9:30 instead of 09:30. When I do SUBSTR(TIME,1,2) I get 9:, where I need to LPAD 0 in the front and make it 09.

Below is the way I did, I get the data but I need short version of this.
SELECT CASE WHEN SUBSTR('09:30',1,2) LIKE '%:%' THEN '0' || REPLACE(SUBSTR('9:30',1,2),':','')
ELSE SUBSTR('09:30',1,2) END
FROM DUAL;



Thanks,
ASPDEV
Comment
Watch Question

CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Ryan Chong,

Your answer is correct and I need one more thing, how I can do same for Minutes string, like if string comes like this 09:3, for Minutes append 0 on the right as 30. Sometime, I may get 9:3, the minute should be 30(not 03).


CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Alex [***Alex140181***]Software Developer
CERTIFIED EXPERT

Commented:
Let's take a big step "backwards" and take a look at the issue behind the problem ;-)
Where do you get that time from?! If its source is some date field, why do you not just make use of
select to_char(sysdate, 'hh24:mi') from dual;
where "sysdate" would be your date field instead....
And if it's not a date-based source, I still would convert it to a date column and then do the "to_char" instead of using "rpad" and (the expensive) "regexp_substr", which is imho totally unnecessary!

Author

Commented:
Alex,
I like your suggestion, but the source I get as string 9:30(time only). How can I convert that to DATE and then the TO_CHAR.


awking00Information Technology Specialist
CERTIFIED EXPERT

Commented:
Using sql only (i.e.  no need for PL/SQL), you might use -
select
lpad(substr('9:3',1,instr('9:3',':')),'0') as hour,
rpad(substr('9:3',instr('9:3',':')+1),'0') as minute
from dual;
Obviously substituting '9:3' with your time field and dual with your table name.

Author

Commented:
awking00,

You sql code doesn't work, I get BLANK data for both hour and minute columns
SELECT lpad(substr('9:30',1,instr('9:30',':')),'0') as hour,
rpad(substr('9:30',instr('9:30',':')+1),'0') as minute
from dual;
Software Developer
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Alex [***Alex140181***]Software Developer
CERTIFIED EXPERT

Commented:
Using 12.1 >, this could look like:

with
  function to_date_x(pi_date_str in varchar2, pi_date_format in varchar2) return date is
  begin
    return to_date(pi_date_str, pi_date_format);
  exception
    when others then
      return null;
  end;
select time_string,
       to_date_x(time_string, 'hh24:mi') time_date,
       to_char(to_date_x(time_string, 'hh24:mi'), 'hh24:mi') time_date_char
  from (select '9:30' time_string
          from dual
        union
        select '10:30' time_string
          from dual
        union
        select '9:3' time_string
          from dual
        union
        select '1:30' time_string
          from dual
        union
        select '9:6' time_string
          from dual
        union
        select '25:30' time_string
          from dual
        union
        select '9:66' time_string
          from dual)

Author

Commented:
Thanks to both Ryan & Alex
Alex [***Alex140181***]Software Developer
CERTIFIED EXPERT

Commented:
You're welcome!
awking00Information Technology Specialist
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
awking00Information Technology Specialist
CERTIFIED EXPERT

Commented:
You might still give my last approach a try.

Author

Commented:
awking00, now it worked too.

Thanks,
ASPDEV
awking00Information Technology Specialist
CERTIFIED EXPERT

Commented:
I'm glad you tried it. Thanks.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.