How to use REGEXP in SQLite3 to extract matched string (as a column value)

Question

Is there a way to use REGEXP to return not just whether or not there was a match (i.e. 1 or 0), but the actual match itself. For example, if I have a column of URLs and I want to extract the matched portion of a URL (e.g. the domain):

SELECT url REGEXP '\w+\.com' AS domain
FROM urls
GROUP by domain;

Not sure if there are differences between REGEXP extensions, but FWIW, I'm using the one found in DB Browser for SQLite

Does not this return matching part `SELECT * FROM urls WHERE url REGEXP '\w+\.com'` ? — revo, Aug 23 '16 at 17:21
@revo..that returns a row if the pattern matches,else it doesn't. The OP is asking for the pattern itself. — Vamsi Prabhala, Aug 23 '16 at 17:22

score 2 · Accepted Answer · answered Aug 24 '16 at 06:58

2

The REGEXP operator returns just a boolean value, whether the text matches or not.

If you want to get more information, you have to write some other function.

answered Aug 24 '16 at 06:58

CL.

173,858
17
217
259

Accepting this as the answer, as I haven't found any other (official) alternative, and the REGEXP operator is as you say. Thanks! – dancow Aug 24 '16 at 11:19

How to use REGEXP in SQLite3 to extract matched string (as a column value)

1 Answers1

Linked