SQL - get specific text from string

Question

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?

GMB · Accepted Answer · 2019-12-14 21:00:11Z

2

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

edited Dec 14, 2019 at 21:00

answered Dec 14, 2019 at 20:57

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

GMB Over a year ago

@OksanaOk: welcome! Just to check, what if you use \\w instead of \w?

McNets · Accepted Answer · 2019-12-14 21:09:54Z

0

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

answered Dec 14, 2019 at 21:09

McNets

10.9k3 gold badges39 silver badges70 bronze badges

Collectives™ on Stack Overflow

SQL - get specific text from string

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related