I was asked to find a list of posts in a Wordpress install where the body text contained a certain string. Subsequently, I discovered how bad the search is in the post listing screen and decided it would be easier to find them in the database. To just grab the useful information, I used the following SQL:
You need to make sure that you change wp_ to the database prefix your site is using. The domain name is so we get a nice list of urls in the output so we can visually check the pages. The string is the text you want to search for, the % are wildcards and need to stay (unless you only want to find a string at the start or end of the text). After running the query you will get a list like the following image with the id of each post, the title of the post (which you should be able to use in the Wordpress admin area to find the post, fingers crossed) and a link to the page on the frontend of the site (Where you can use the edit link in the admin bar if you are logged in).
I used post_name after the domain name as the permalink settings are set to use the post_name slug as the url. You could change that to CONCAT('http://www.domain.com/?p=',ID) if you are using other settings and Wordpress should redirect you to the correct page.
You can export the results to a CSV file which can be opened up as a spreadsheet if someone asked you to get them a list.
