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