Extracting string between two characters or string using mysql REGEXP_SUBSTR

Question

I need help in extracting the following string. I have tried many solutions but this one is the closest. But still not what I require. Any help is appreciated.

Sample URL: 'https://mywebsite/path/?utm_source=google&utm_medium=cpc&gclid=123abc'

Required Result:

utm_source	utm_medium	gclid
google	cpc	123abc

The following example for gclid gives me gclid=123abc as a result, while I require to extract 123abc

SELECT l.url, REGEXP_SUBSTR(l.url, 'gclid=([^&]*)') as data
FROM mydatabase.mytable AS l
WHERE Date(l.registration_date) >= '2021-06-15'
AND REGEXP_SUBSTR(l.url, 'gclid=([^&]*)') is not null

I need to parse the other two fields also like utm_source and utm_medium.

You can try REGEXP_SUBSTR(l.url, '(?<=[?&]gclid=)[^&#]+') and similarly REGEXP_SUBSTR(l.url, '(?<=[?&]utm_source=)[^&#]+') and REGEXP_SUBSTR(l.url, '(?<=[?&]utm_medium=)[^&#]+') — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Jun 21, 2021 at 10:23
gclid=12345 in sample URL but gclid=123abc in required output. Typo? — Akina
– Akina, Commented Jun 21, 2021 at 10:30

Wiktor Stribiżew · Accepted Answer · 2021-06-21 10:40:22Z

2

You can use lookbehinds here to exclude the static text from your matches:

REGEXP_SUBSTR(l.url, '(?<=[?&]gclid=)[^&#]+')
REGEXP_SUBSTR(l.url, '(?<=[?&]utm_source=)[^&#]+')
REGEXP_SUBSTR(l.url, '(?<=[?&]utm_medium=)[^&#]+')

See a sample regex demo.

Details:

(?<=[?&]gclid=) - a positive lookbehind that matches a location that is immediately preceded with ? or & (this makes sure we only match the full query param key) and then gclid=
[^&#]+ - one or more chars other than & and # (consumed and returned as a match value).

answered Jun 21, 2021 at 10:40

Wiktor Stribiżew

631k41 gold badges502 silver badges632 bronze badges

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

Comments

Akina · Accepted Answer · 2021-06-21 10:27:37Z

2

SET @URL := 'https://mywebsite/path/?utm_source=google&utm_medium=cpc&gclid=12345';

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(@URL, 'utm_source=', -1), '&', 1) utm_source,
       SUBSTRING_INDEX(SUBSTRING_INDEX(@URL, 'utm_medium=', -1), '&', 1) utm_medium,
       SUBSTRING_INDEX(SUBSTRING_INDEX(@URL, 'gclid=', -1), '&', 1) gclid;

utm_source	utm_medium	gclid
google	cpc	12345

db<>fiddle here

answered Jun 21, 2021 at 10:27

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Collectives™ on Stack Overflow

Extracting string between two characters or string using mysql REGEXP_SUBSTR

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related