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
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.
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.
SELECT. It doesn't actually update the field. So you want to return http://?REGEXP functions only return true or false. Not the match. You would need to build this into the query yourself: IF(URL REGEXP 'http://', 'http://', '') or just alias the field and interpret it in your results: URL REGEXP 'http://' AS has_http. If you have many matches, I suggest doing this with a different technology.