SQL – get specific text from string

I have column with this strings:

['autre__renvoi_vers_un_mail', 'contact_type__email']
['contact_type__email', 'internal__shop', 'uk']

I need to get from the string ONLY the contact_type__* part (appears only once in each row)

contact_type__email
contact_type__email

Any suggestions?

Answer

You could use regexp function regexp_extract(), like:

regexp_extract(mycol, 'contact_type__[a-zA-Z0-9_]*')

This will match on string 'contact_type__ followed by a sequence of letters, numbers, or underscore (which are commonly defined as word characters).