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 |
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 |
Solomon Yakobson
Messages: 3284 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 #665889 is a reply to message #665887] |
Thu, 28 September 2017 12:29 |
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
|
|
|
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 |
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 |
Solomon Yakobson
Messages: 3284 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
|
|
|
Goto Forum:
Current Time: Mon Sep 23 20:35:17 CDT 2024
|