CODEWITHSUNDEEP
mysqlsqldatabasewordpress
shasi kanth
shasi kanth

Reputation: 7102

mysql search string in table ignoring links

I am trying to search the wordpress posts in my database, that match a string.

However i want to get the rows that have the matching words in them as a plain text, but not the ones that have the word embedded inside a link.

For example, if a post has the following text in 'wp_posts' table of database:

'This is a Test page: <a href="http://localhost/test-page">About Test</a>'.

And i search for the word 'about' in my custom query:

SELECT * FROM `wp_posts` WHERE post_content like '%about%';

so i am getting the above post as the result. Is there a way i can ignore the posts that have the search string embedded inside a link, and fetch other posts which have the search string as part of a normal string?

I tried a REGEX query, with no luck:

SELECT * FROM `wp_posts` WHERE post_content REGEXP '[[:<:]]about[[:>:]]';

Upvotes: 1

Views: 180

Answers (3)

lifeng.luck
lifeng.luck

Reputation: 601

May be this :

SELECT * FROM `wp_posts` WHERE post_content LIKE '%about%' AND post_content NOT REGEXP '>[^>]*about[^<]*<';

Upvotes: 3

Linga
Linga

Reputation: 10573

Try this

SELECT * FROM `wp_posts` WHERE post_content REGEXP '^[^<>]+about';

Here is the Fiddle

Upvotes: 1

lordkain
lordkain

Reputation: 3109

in sqlfiddle it works fine!

http://sqlfiddle.com/#!2/f1697/2

Create script

CREATE TABLE supportContacts 
(
    test longtext
);

INSERT INTO supportContacts(test) VALUES ('This is a Test page: <a href="http://localhost/test-page">About Test</a>')

Select

select * from supportContacts where test like '%about%'

Upvotes: 0

Related Questions