regex101: Python Substitution Workspace

Regular Expression

\B:([^\W\d_]\w*)

New

select  ... from (
select  ... from ( 
select  ... from  table1
where session_started between  toDateTime(:DatumOd) and toDateTime(:DatumDo)
and session_id in (select distinct ...  from table2
    where   session_start>=toDateTime('2019-01-01 10:11:12') and session_module=:channel
            and session_start between  toDateTime(:DatumOd) and toDateTime(:DatumDo)
            and ( domain_name in (:domain) or 'All domains' in (:domain) )
            and (technology in (:technology) or 'All' in (:technology))
            and (CASE when session_principal_role='Self care' then agent_name else session_principal_role end in  (:application) 
            or 'All' in (:application) )  )
order by session_id desc , execution_id desc, step_started desc, step_id desc)
) where step_type=:step_type and ...

Extraction

select  ... from (
select  ... from ( 
select  ... from  table1
where session_started between  toDateTime(:DatumOd) and toDateTime(:DatumDo)
and session_id in (select distinct ...  from table2
    where   session_start>=toDateTime('2019-01-01 10:11:12') and session_module=:channel
            and session_start between  toDateTime(:DatumOd) and toDateTime(:DatumDo)
            and ( domain_name in (:domain) or 'All domains' in (:domain) )
            and (technology in (:technology) or 'All' in (:technology))
            and (CASE when session_principal_role='Self care' then agent_name else session_principal_role end in  (:application) 
            or 'All' in (:application) )  )
order by session_id desc , execution_id desc, step_started desc, step_id desc)
) where step_type=:step_type and ...