1

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

Vad Boro
  • 77
  • 10

2 Answers2

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)
Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
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;
Dan Getz
  • 17,002
  • 2
  • 23
  • 41