CODEWITHSUNDEEP
regexstringpostgresqlreplace
franco_b
franco_b

Reputation: 878

Postgres use regexp_replace to match character in a certain position and replace

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.

Upvotes: 0

Views: 1067

Answers (2)

Gary_W
Gary_W

Reputation: 10360

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;

SQL Fiddle example

More regex info

Upvotes: 2

Michał Zaborowski
Michał Zaborowski

Reputation: 4387

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');

Upvotes: 1

Related Questions