CODEWITHSUNDEEP
sqlregexgoogle-bigquery
Oksana Ok
Oksana Ok

Reputation: 555

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?

Upvotes: 0

Views: 65

Answers (2)

McNets
McNets

Reputation: 10807

Given this example:

create table t (col varchar(100));
insert into t values
('[''autre__renvoi_vers_un_mail'', ''contact_type__email'']'),
('[''contact_type__email'', ''internal__shop'', ''uk'']');

You can use:

select
    substring 
    (
     col,
     charindex('contact_type', col) + 14,
     charindex('''', col, charindex('contact_type', col)) - charindex('contact_type', col) - 14
    )
from
    t;

db<>fiddle here

Upvotes: 0

GMB
GMB

Reputation: 222482

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

Upvotes: 2

Related Questions