MySQL & Regex: match the whole word only or skip URLs?

I use the query below for searching,

SELECT
pg_id AS ID, 
pg_url AS URL,
pg_title AS Title,
pg_content_1 AS Content_1,
pg_content_2 AS Content_2,
parent_id AS Parent_id,

EXTRACT(DAY FROM pg_created) AS Date,
EXTRACT(MONTH FROM pg_created) AS Month,
EXTRACT(YEAR FROM pg_created) AS Year

FROM root_pages

WHERE root_pages.pg_cat_id = '2'
AND root_pages.parent_id != root_pages.pg_id
AND root_pages.pg_hide != '1'
AND root_pages.pg_url != 'cms'
AND root_pages.pg_content_1 REGEXP '[[:<:]]".$search."[[:>:]]'
OR root_pages.pg_content_2 REGEXP '[[:<:]]".$search."[[:>:]]开发者_如何学C'

ORDER BY root_pages.pg_created DESC

I works fine but I don't want it to search the keyword in the URL address such as,

If I search for the keyword of home, the query will search any matches of 'home' in the URLs below and return them as the result too:

http://epp.eurostat.ec.europa.eu/xx/eurostat/home/

http://ec.europa.eu/home-affairs/doc_centre/xx.pdf

How can I fix the query so that it won't match the keyword in an URL or it should match the whole word only?

Thanks.

Try using this strip_tags implementation for MySQL

CREATE FUNCTION strip_tags( x longtext) RETURNS longtext
LANGUAGE SQL NOT DETERMINISTIC READS SQL DATA
BEGIN
    DECLARE sstart INT UNSIGNED;
    DECLARE ends INT UNSIGNED;
    SET sstart = LOCATE('<', x, 1);
    REPEAT
        SET ends = LOCATE('>', x, sstart);
        SET x = CONCAT(SUBSTRING( x, 1 ,sstart -1) ,SUBSTRING(x, ends +1 )) ;
        SET sstart = LOCATE('<', x, 1);
    UNTIL sstart < 1 END REPEAT;
return x;
END;

(found here)

And then your query would look like this:

AND STRIP_TAGS(root_pages.pg_content_1) REGEXP '[[:<:]]".$search."[[:>:]]'
OR STRIP_TAGS(root_pages.pg_content_2) REGEXP '[[:<:]]".$search."[[:>:]]'

However, this implementation may be slow and unreliable, so I'd suggest a different approach:

1. create a new column, for example search
2. insert there raw (=php strip_tagged) search data for each row in your table (may even be a combination of the two columns you use for searching)
3. Then you can use query like this one - SELECT col1, col2 FROM table WHERE search LIKE '%your_search_expression%' or even fulltext keys if you want. (use LIKE instead of REGEXP as it is faster).

EDIT:

Found my own solution and it seems to work fine as it skips searching the text in the column with http://

SELECT
pg_id AS ID, 
pg_url AS URL,
pg_title AS Title,
pg_content_1 AS Content_1,
pg_content_2 AS Content_2,
parent_id AS Parent_id,

EXTRACT(DAY FROM pg_created) AS Date,
EXTRACT(MONTH FROM pg_created) AS Month,
EXTRACT(YEAR FROM pg_created) AS Year

FROM root_pages

WHERE root_pages.pg_cat_id = '2'
AND root_pages.parent_id != root_pages.pg_id
AND root_pages.pg_hide != '1'
AND root_pages.pg_url != 'cms'
AND root_pages.pg_content_1 LIKE '%".$search."%'
OR root_pages.pg_content_2 LIKE '%".$search."%'

AND root_pages.pg_content_1 NOT LIKE '%http://%'
AND root_pages.pg_content_2 NOT LIKE '%http://%'

ORDER BY root_pages.pg_created DESC