convert string to HH24:MM:SS in oracle

how to convert  141059  to HH24:MM:SS in oracle
Who is Participating?
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Are you just wanting the colons added?

select regexp_replace('141059','([0-9]{2})([0-9]{2})([0-9]{2})','\1:\2:\3') from dual;

If not that, what exactly are you wanting to do?
slightwv (䄆 Netminder)Connect With a Mentor Commented:
It's overkill but you could turn it into a date then back into a string:
select to_char(to_date('141059','HH24MISS'),'HH24:MI:SS') from dual;
PortletPaulConnect With a Mentor freelancerCommented:
if converting to date then you might need to validate the string i.e. 141066 would fail

mind you just inserting colons would not fail but would look odd (14:10:66)
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

How is this conversion going to be used? Just for display, or input to a procedure or insert statement, or to apply some kind of date math, or what?
slightwv (䄆 Netminder) Commented:

Please explain why you accepted the post you did as the solution.  That post was a question and really did nothing to answer the question asked.

If it was just to get around the new question lock, this is inappropriate.

If you do not post back in the next day or two, I'll have this addressed by the Moderators.
500 to 0 :-( No problem
I'm not at all displeased with not getting any credit for my comment asking questions. I just thought it was funny to first be credited with all of the points (admittedly undeservingly so) then none. I just wish the asker had answered the questions so that we could help him if the result needed to be something other than a just string of characters.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.