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

closed as unclear what you're asking by dezso, Colin 't Hart, RLF, Vérace, RolandoMySQLDBA Jun 22 '15 at 15:47

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 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
  • 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.