Регулярное выражение находящее текст после n-го значения подстроки?

Question

[marselabdullin]

Нужно найти значение подстроки после 3 "select" и перед "Stage."

Текстовый файл:

select
	ds.Job_DmpKey,
	ds.LoadDTM,
	ds.HashDiff,
	ds.SnapshotDT,
	ds.Ctl_Subtype_CD,
	ds.Ctl_LogisticObject_CD,
	ds.Ctl_Description_DSC,
	ds.Ctl_VATProdPostingGroup_CD,
	ds.Ctl_SearchDescription_DSC,
	ds.Ctl_DocumentDescription_DSC
from (
	select 
		hash(ds.Job_DmpKey) as Job_DmpKey,
		'{{ macros.datetime.now() }}' as LoadDTM,
		hash(
			ds.Ctl_Subtype_CD,
			ds.Ctl_LogisticObject_CD,
			ds.Ctl_Description_DSC,
			ds.Ctl_VATProdPostingGroup_CD,
			ds.Ctl_SearchDescription_DSC,
			ds.Ctl_DocumentDescription_DSC
		) as HashDiff,
		'{{ ds }}' as SnapshotDT,
		ds.Ctl_Subtype_CD,
		ds.Ctl_LogisticObject_CD,
		ds.Ctl_Description_DSC,
		ds.Ctl_VATProdPostingGroup_CD,
		ds.Ctl_SearchDescription_DSC,
		ds.Ctl_DocumentDescription_DSC
	from (
		select
			'Job*naCitilink*' || cast(stg."No_" as varchar(100)) 		as Job_DmpKey,
			cast(stg.Subtype as varchar(40))							as Ctl_Subtype_CD,
			cast(stg."Logistic Object Code" as varchar(40))				as Ctl_LogisticObject_CD,
			cast(stg."Description"  as varchar(4000))					as Ctl_Description_DSC,
			cast(stg."VAT Prod_ Posting Group" as varchar(40))			as Ctl_VATProdPostingGroup_CD,
			cast(stg."Search Description"  as varchar(4000))			as Ctl_SearchDescription_DSC,
			cast(stg."Document Description"  as varchar(4000))			as Ctl_DocumentDescription_DSC
			
		from Stage.STG_Ctl_Job stg	
		where 
			-- catchup_snapshotdt_start
			stg.SnapshotDT = '{{ ds }}'
			-- catchup_snapshotdt_end
		limit 1 over (partition by stg.No_ order by stg.LoadDTM desc)
	) ds
) ds 
left join (
		SELECT
			sat.Job_DmpKey,
			sat.HashDiff 
		from RawVault.RV_Sat_Ctl_Job sat
		where 
			-- catchup_snapshotdt_start
			sat.SnapshotDT <= '{{ ds }}'
			-- catchup_snapshotdt_end
		limit 1 over (partition by sat.Job_DmpKey order by sat.LoadDTM desc)
) tgt
	on ds.Job_DmpKey = tgt.Job_DmpKey
	and ds.HashDiff = tgt.HashDiff
	
where tgt.Job_DmpKey is null;

В данном случае ответом будет:

'Job*naCitilink*' || cast(stg."No_" as varchar(100)) 		as Job_DmpKey,
cast(stg.Subtype as varchar(40))							as Ctl_Subtype_CD,
cast(stg."Logistic Object Code" as varchar(40))				as Ctl_LogisticObject_CD,
cast(stg."Description"  as varchar(4000))					as Ctl_Description_DSC,
cast(stg."VAT Prod_ Posting Group" as varchar(40))			as Ctl_VATProdPostingGroup_CD,
cast(stg."Search Description"  as varchar(4000))			as Ctl_SearchDescription_DSC,
cast(stg."Document Description"  as varchar(4000))			as Ctl_DocumentDescription_DSC

Comments

[Кирилл Пальчевский]

А можно увидеть начальный вариант кода? Или ошибку, которая появляется ?

Answer 1

[0xD34F]

results = re.findall(r'(?<=SELECT).+?(?=FROM)', string, re.I | re.DOTALL)
print(results[2])

Answer 2

[Кирилл Пальчевский]

text = """select
  ds.Job_DmpKey,
  ds.LoadDTM,
  ds.HashDiff,
  ds.SnapshotDT,
  ds.Ctl_Subtype_CD,
  ds.Ctl_LogisticObject_CD,
  ds.Ctl_Description_DSC,
  ds.Ctl_VATProdPostingGroup_CD,
  ds.Ctl_SearchDescription_DSC,
  ds.Ctl_DocumentDescription_DSC
from (
  select 
    hash(ds.Job_DmpKey) as Job_DmpKey,
    '{{ macros.datetime.now() }}' as LoadDTM,
    hash(
      ds.Ctl_Subtype_CD,
      ds.Ctl_LogisticObject_CD,
      ds.Ctl_Description_DSC,
      ds.Ctl_VATProdPostingGroup_CD,
      ds.Ctl_SearchDescription_DSC,
      ds.Ctl_DocumentDescription_DSC
    ) as HashDiff,
    '{{ ds }}' as SnapshotDT,
    ds.Ctl_Subtype_CD,
    ds.Ctl_LogisticObject_CD,
    ds.Ctl_Description_DSC,
    ds.Ctl_VATProdPostingGroup_CD,
    ds.Ctl_SearchDescription_DSC,
    ds.Ctl_DocumentDescription_DSC
  from (
    select
      'Job*naCitilink*' || cast(stg."No_" as varchar(100)) 		as Job_DmpKey,
      cast(stg.Subtype as varchar(40))							as Ctl_Subtype_CD,
      cast(stg."Logistic Object Code" as varchar(40))				as Ctl_LogisticObject_CD,
      cast(stg."Description"  as varchar(4000))					as Ctl_Description_DSC,
      cast(stg."VAT Prod_ Posting Group" as varchar(40))			as Ctl_VATProdPostingGroup_CD,
      cast(stg."Search Description"  as varchar(4000))			as Ctl_SearchDescription_DSC,
      cast(stg."Document Description"  as varchar(4000))			as Ctl_DocumentDescription_DSC
      
    from Stage.STG_Ctl_Job stg	
    where 
      -- catchup_snapshotdt_start
      stg.SnapshotDT = '{{ ds }}'
      -- catchup_snapshotdt_end
    limit 1 over (partition by stg.No_ order by stg.LoadDTM desc)
  ) ds
) ds 
left join (
    SELECT
      sat.Job_DmpKey,
      sat.HashDiff 
    from RawVault.RV_Sat_Ctl_Job sat
    where 
      -- catchup_snapshotdt_start
      sat.SnapshotDT <= '{{ ds }}'
      -- catchup_snapshotdt_end
    limit 1 over (partition by sat.Job_DmpKey order by sat.LoadDTM desc)
) tgt
  on ds.Job_DmpKey = tgt.Job_DmpKey
  and ds.HashDiff = tgt.HashDiff
  
where tgt.Job_DmpKey is null;"""

new_text = text.rpartition('select')[2].partition('from ')[0]

print(new_text)

Ответ:

'Job*naCitilink*' || cast(stg."No_" as varchar(100)) as Job_DmpKey,
cast(stg.Subtype as varchar(40)) as Ctl_Subtype_CD,
cast(stg."Logistic Object Code" as varchar(40)) as Ctl_LogisticObject_CD,
cast(stg."Description" as varchar(4000)) as Ctl_Description_DSC,
cast(stg."VAT Prod_ Posting Group" as varchar(40)) as Ctl_VATProdPostingGroup_CD,
cast(stg."Search Description" as varchar(4000)) as Ctl_SearchDescription_DSC,
cast(stg."Document Description" as varchar(4000)) as Ctl_DocumentDescription_DSC

Answer 3

[Andrey]

r = re.findall("select(.+?)from", s, re.IGNORECASE | re.DOTALL)
print(r[2])

или