Home » SQL & PL/SQL » SQL & PL/SQL » regexp_replace append 0 to single digit date,month,hour,minute,second
regexp_replace append 0 to single digit date,month,hour,minute,second [message #665870] Wed, 27 September 2017 14:45 Go to next message
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
Senior Member

There can be 5 separators.

1. (space)
2. -
3. ,
4. :
5. /

I am trying to separate the date string on those separators, and then see if any part has just one number, and append it with '0' and re-combine using same separator. And also don't want to append 0 in am/pm (a/p) string. Just append 0, when the separated sub-string is a number.


For example:
In case of 8/19/2017

8 and 19 and 2017 will become 08 and 19 and 2017 and then after combining with the same separators, it will become 08/19/2017

In case of 8/9/17 8:9:2 a

should be converted to 08/09/17 08:09:02 a

I was trying below code, that didn't worked.

SELECT REGEXP_REPLACE ('9/18/2017 8:30 A',
                       '([[:digit:]]+)(\/|\:|\ |\-)',
                       CONCAT (LPAD ('\1', 2, '0'), '\2')) from dual;

Thanks,
Manu
Re: regexp_replace append 0 to single digit date,month,hour,minute,second [message #665871 is a reply to message #665870] Wed, 27 September 2017 18:54 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Oracle REGEXP doesn't support lookahead/lookbehind, so you'll have to do it with two REGEXP_REPLACE:

with t as (
           select '8/19/2017' str from dual union all
           select '8/9/17 8:9:2 a' from dual
          )
select  str,
        regexp_replace(regexp_replace(str,'(\d+)','0\1'),'0(\d\d)','\1') new_str
  from  t
/

STR            NEW_STR
-------------- --------------------
8/19/2017      08/19/2017
8/9/17 8:9:2 a 08/09/17 08:09:02 a

SQL>

SY.
Re: regexp_replace append 0 to single digit date,month,hour,minute,second [message #665887 is a reply to message #665871] Thu, 28 September 2017 10:30 Go to previous messageGo to next message
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
Senior Member

Thanks a ton for the logic. Smile
Re: regexp_replace append 0 to single digit date,month,hour,minute,second [message #665889 is a reply to message #665887] Thu, 28 September 2017 12:29 Go to previous messageGo to next message
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
Senior Member

WITH t AS (SELECT '20170101 23:01:01' str FROM DUAL)
SELECT str,
       REGEXP_REPLACE (REGEXP_REPLACE (str, '(\d+)', '0\1'), '0(\d\d)', '\1')
           new_str
  FROM t;

failed Sad
Re: regexp_replace append 0 to single digit date,month,hour,minute,second [message #665890 is a reply to message #665889] Thu, 28 September 2017 13:33 Go to previous messageGo to next message
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
Senior Member

Do you think this should work:

WITH t AS (SELECT '20170101 23:01:01' str FROM DUAL)
SELECT str,
       REGEXP_REPLACE (str, '(\d+)', '0\1'),
       REGEXP_REPLACE (REGEXP_REPLACE (str, '(\d+)', '0\1'), '(^| |-|,|:|/)0(\d\d)', '\1\2')
           new_str
  FROM t;
Re: regexp_replace append 0 to single digit date,month,hour,minute,second [message #665891 is a reply to message #665889] Thu, 28 September 2017 19:11 Go to previous message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Oops,

somehow I lost + in '0(\d\d+)' in my post. It should be:

with t as (
           select '8/19/2017' str from dual union all
           select '8/9/17 8:9:2 a' from dual union all
           select '20170101 23:01:01' from dual
          )
select  str,
        regexp_replace(regexp_replace(str,'(\d+)','0\1'),'0(\d\d+)','\1') new_str
  from  t
/

STR               NEW_STR
----------------- --------------------
8/19/2017         08/19/2017
8/9/17 8:9:2 a    08/09/17 08:09:02 a
20170101 23:01:01 20170101 23:01:01

SQL>

SY.

[Updated on: Thu, 28 September 2017 19:45]

Report message to a moderator

Previous Topic: Cuimulative totals
Next Topic: Restrict the other session for tables
Goto Forum:
  


Current Time: Thu Mar 28 20:35:43 CDT 2024