Using mysql Regexp in a select statement not the where clause

Question

So what i want to do is use a Regex to select only certain parts of data from a column

Example :

SELECT URL REGEXP 'http://' from websitelist --(website list is a list of URL's)

If I run that against the table it returns 1 foreach row in which 'htt://' was found, what I want is to return the string that matches the regexp

Answer 1

The REGEXP operator performs a match and returns 0 or 1 based on whether the string matched the expression or not. It does not provide a way to extract the matched portion. I don't think that there is any way to extract the matched portion.

Answer 2

You could use just use string functions if it's as simple as your example - just removing http://

SELECT REPLACE(URL, 'http://', '') AS url FROM websitelist;

Probably faster as there is overhead for the REGEX engine.