Postgresql regexp_substring

Question

I have a text as

Revision:3336179e1ebaa646cf281b7fb9ff36d1b23ce710$ modified $RevDate:10-04-2017 11:43:47$ by $Author:admin

Could you help me out with writing sql query that would get the first 6 chars of revision (333617) and date of RevDate (10-04-2017 11:43:47)? Thanks in advance.

I tried to get revision as

select (regexp_matches ('$Revision:3336179e1ebaa646cf281b7fb9ff36d1b23ce710$ modified $RevDate:10-04-2017 11:43:47$ by $Author:admin$', 
'^\$Revision:(a-z0-9+)\$'))[1]

No luck

Vao Tsun · Accepted Answer · 2017-04-19 08:07:22Z

1

I'd rather go with substr and split_part (they are faster if I'm not mistaken):

t=# with v as (select 'Revision:3336179e1ebaa646cf281b7fb9ff36d1b23ce710$ modified $RevDate:10-04-2017 11:43:47$ by $Author:admin'::text l)
select substr(l,length('Revision:')+1,6),substr(split_part(l,'$',3),length('RevDate:')+1)
from v;
 substr |       substr
--------+---------------------
 333617 | 10-04-2017 11:43:47
(1 row)

answered Apr 19, 2017 at 8:07

Vao Tsun

52.5k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dan Getz · Accepted Answer · 2017-04-19 09:21:35Z

0

If regular expressions are preferred, the fixed-up expression would be:

select a[1] as revision, a[2] as date from (
  select regexp_matches('$Revision:3336179e1ebaa646cf281b7fb9ff36d1b23ce710$ modified $RevDate:10-04-2017 11:43:47$ by $Author:admin$',
 'Revision:([a-z0-9]{6}).*RevDate:([^\$]+)') a ) atbl;

edited Apr 19, 2017 at 9:21

answered Apr 19, 2017 at 8:22

Dan Getz

18.3k2 gold badges25 silver badges42 bronze badges

2 Comments

Vad Boro Over a year ago

the time part is not included in a[2], correct pattern would be 'Revision:([a-z0-9]{6}).*RevDate:([^\$]+)', thnks for your help

Dan Getz Over a year ago

Thanks. Fixed answer

Collectives™ on Stack Overflow

Postgresql regexp_substring

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related