1

I have a string in which need to extract the date,

Example: S01_XXX001_000_min_01_20150526T123440583.arc , S01_XXX001_000_20150526T123440583_min_01.arc

based on the pattern like 'Numeric T Numeric',

I am trying to extract with the below sql , can any one help me.

SQL:

select  substring('S01_ZAR001_000_min_01_20150526T123440583.arc','^\d')

select  substring('SAPCC_XX_ZZZ_BitClass_XXX_20150623T45656566456_XXX_XX_XXXXXX.arc' like '_%T%_',1,8)

select  substring('SAPCC_XX_ZZZ_BitClass_XXX_201506235T335656_XXX_XX_XXXXXX.arc','[^_]+([^0-9]{1,8})$')

SELECT SUBSTRING('XY1234Z', 'Y*([0-9]{1,3})');
  • Why the mysql and oracle tags? – a_horse_with_no_name Jun 22 '15 at 7:29
  • pattern matching is mostly similar , Just looking for some hope – William R Jun 22 '15 at 7:36
  • 1
    No it's not "mostly similar". The syntax is completely different between those three DBMS. – a_horse_with_no_name Jun 22 '15 at 7:38
  • Isn't the part after the 'T' the time? From your examples it looks like it isn't - in this case, you just want to discard it? – dezso Jun 22 '15 at 8:19
  • One more: 201506235 is not a valid date in you third example. – dezso Jun 22 '15 at 8:26
0
select substr(
        substring('SAPCC_XX_ZZZ_Bit1T1Class_XXX_201506235T335656_XXX_XX_XXXXXX'
                  from 
                  '_[0-9]+T[0-9]+_'
                 ), 2, 8
             )::date
| improve this answer | |
  • 1
    Would you mind to explain this a bit? – dezso Jun 22 '15 at 8:07
  • 1
    needs explaining – James Anderson Jun 22 '15 at 8:39
  • extracting the data from the file (i.e 2015-06-23) – William R Jun 24 '15 at 9:44

Not the answer you're looking for? Browse other questions tagged or ask your own question.