I need to check if the first 2 character of a string match with '00' or '01' or '02 and replace with '24' or '25' or '26' respectively.
So for example replace
'02:00:00' with '26:00:00'
I try:
SELECT replace(replace(replace('01:01:00'::varchar, '00:', '24:'),'01:', '25:'), '02:', '26:')
doesn't work because it returns:
"25:25:00"
I want match condition only for the first 2 character:
"25:01:00"
Maybe regexp_replace is the right way but I cannot find a solution.
Here's the PostgreSQL version of my previous Oracle example that logically does the same thing. Sometimes you need to RTFM! :-) It returns the first element from the first group using the substring() function, which is tested, swapped with the value you want to replace, the concatenated with the rest of the string which is returned from a group from the second substring() call.
select
case substring(str from '^(\d{2}:)')
when '00:' then '24:'
when '01:' then '25:'
when '02:' then '26:'
else substring(str from '^(\d{2}:)')
end || substring(str from '^\d{2}:(\d{2}:\d{2})$') as fixed
from tbl;
regexp_replace('Thomas', '.[mN]a.', 'M')select regexp_replace('01:01:03', '([0-9]{2})(:[0-9]{2}:[0-9]{2})', 'aa\2');
There are two groups first with 2 numbers and second with everything else. Replace means print new string, and everything else / second group.
UPDATE
Thanks @franco_b. Here is version which updates some more entries:
select (regexp_replace('01:01:03', '([0-9]{2})(:[0-9]{2}:[0-9]{2})', '\1')::int + 24)::text||regexp_replace('01:01:03', '([0-9]{2})(:[0-9]{2}:[0-9]{2})', '\2');
SELECT regexp_replace(regexp_replace(regexp_replace('01:01:03', '([00]{2})(:[0-9]{2}:[0-9]{2})', '24\2'), '([01]{2})(:[0-9]{2}:[0-9]{2})', '25\2') , '([02]{2})(:[0-9]{2}:[0-9]{2})', '25\2')'aa\2'.
\1, \2 - are references to groups that are defined in the regex. In other words every ( .... ) makes new group, and then you can refer to it.