Mysql regex looking for links

Question

How to get this expression work:

SELECT post_content 
FROM wp_posts 
WHERE post_content REGEXP 'http:\/\/www.google.com\/\?link=.*_1'

I have some posts with some links in this style, so I wont get list of all posts with this links.

With this query I get an empty list.

Table:

| id | wp_posts | other fields....
| 1  | text <a href="http://www.google.de/?link=test_1">Link</a> text | ....

So I have to find the post ID 1

can you show table contents? and what output required?

thumber nirmal
– thumber nirmal

2013-04-06 09:46:31 +00:00
Commented Apr 6, 2013 at 9:46 — thumber nirmal
– thumber nirmal, Commented Apr 6, 2013 at 9:46
@thumbernirmal I added a little example.

Alexander_F
– Alexander_F

2013-04-06 10:10:49 +00:00
Commented Apr 6, 2013 at 10:10 — Alexander_F
– Alexander_F, Commented Apr 6, 2013 at 10:10

Joachim Isaksson · Accepted Answer · 2013-04-06 10:33:46Z

1

Escaping / is not necessary, but you'll need to use \\ to escape your question mark.

Note
Because MySQL uses the C escape syntax in strings (for example, “\n” to represent the newline character), you must double any “\” that you use in your REGEXP strings.

Also, searching for google in the correct country is a good idea :-)

This'll work;

SELECT post_content 
FROM wp_posts 
WHERE post_content REGEXP 'http://www.google.de/\\?link=.*_1';

An SQLfiddle to test with.

answered Apr 6, 2013 at 10:33

Joachim Isaksson

182k28 gold badges297 silver badges307 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mysql regex looking for links

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related