@marselabdullin

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

Нужно найти значение подстроки после 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
  • Вопрос задан
  • 57 просмотров
Решения вопроса 3
0xD34F
@0xD34F
results = re.findall(r'(?<=SELECT).+?(?=FROM)', string, re.I | re.DOTALL)
print(results[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
Ответ написан
Комментировать
seven5674
@seven5674
Старый я уже что бы что-то в себе менять
r = re.findall("select(.+?)from", s, re.IGNORECASE | re.DOTALL)
print(r[2])

или
Ответ написан
Комментировать
Пригласить эксперта
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы