I’m working with a WordPress site that has thousands of posts. Each post contains structured content with specific markers like 【Available Until】. I need to remove everything that comes after this marker using MySQL queries.
Here’s what a typical post looks like:
【Item Name】
Laptop Computer X1
【Available Until】
January 15, 2024
【Additional Info】
Contact support for details
What I want to achieve is keeping only this part:
【Item Name】
Laptop Computer X1
And removing everything from 【Available Until】 onwards:
【Available Until】
January 15, 2024
【Additional Info】
Contact support for details
I’ve been trying different MySQL approaches but can’t figure out the right way to truncate the post content at that specific text marker. What’s the best SQL query to accomplish this bulk operation on the WordPress database?
Watch out for character encoding issues with those Japanese brackets - learned this the hard way on a similar cleanup project. Standard MySQL string functions can act weird with multibyte characters depending on your collation settings. Test LOCATE first to make sure it actually finds your marker text. Some installations store these characters differently than you’d expect. Also think about posts where the marker shows up in the middle of other content instead of being a clean separator. You’re assuming it’s always a section divider, but what if someone stuck that text inside regular paragraphs? I’d check that the marker appears on its own line or has line breaks around it. Export a few sample posts first and look at the raw content - you’ll probably find formatting patterns that’ll break the simple substring approach.
Don’t run bulk SQL on your WordPress database - one bad query can wipe out thousands of posts forever.
I had the same problem last year cleaning up product descriptions across multiple sites. Skip the database gambling and build an automation workflow instead.
Here’s how it works: pulls posts via WordPress API, finds your marker text, cuts content at that spot, then pushes updates back through the API. You get proper validation, automatic backups, and can test on a few posts first.
Process posts in batches so you don’t crash your server. You’ll get a full log of changes and can reverse everything if something goes wrong.
For your 【Available Until】 marker, it’ll find that text, grab everything before it, and dump the rest. Way safer than raw SQL, plus you can rerun it when you add new posts.
I’ve done similar content cleanups on WordPress sites. The SQL approach works, but you’ll hit edge cases that can break your content.
The suggested query is solid but needs safety checks. What if the marker shows up multiple times? What about whitespace before the marker? Hit both problems during a client migration.
Here’s a refined version that handles these:
UPDATE wp_posts
SET post_content = TRIM(SUBSTRING(post_content, 1, LOCATE('【Available Until】', post_content) - 1))
WHERE post_content LIKE '%【Available Until】%'
AND LOCATE('【Available Until】', post_content) > 0
AND post_type = 'post';
TRIM kills trailing whitespace and the LOCATE check stops issues when the function returns 0. Run a SELECT with COUNT(*) first to see how many posts you’re hitting.
Run this during low traffic hours - UPDATE operations can temporarily lock tables on large datasets.
Running SQL directly on WordPress databases scares me. One wrong query and everything’s gone.
I’d handle this cleanup differently. Set up automation that connects to your WordPress API, processes posts in batches, and shows you exactly what’s changing.
The workflow pulls posts through the API, finds the 【Available Until】 marker, strips everything after it, then pushes the update back through WordPress. You get error handling, can pause if something looks off, and everything goes through WordPress validation.
Process 50 posts at a time so you don’t kill your server. Each batch gets logged so you know what changed. Need to rollback? You’ve got the original content stored.
With thousands of posts, this scales way better than manual SQL. Test on a few posts first, then let it churn through your database safely.
Latenode handles WordPress automation like this perfectly. Way safer than raw database queries.
Backup your database first, then try this - use phpMyAdmin or similar to run the query. I’ve seen people wreck their entire site doing bulk updates without testing first. Start with 5-10 posts using LIMIT to see if it works on your content structure before hitting thousands of posts.
Been there with similar cleanup tasks on large WordPress installations. You can do this with one UPDATE query, but backup your database first - this can’t be undone.
Use SUBSTRING and LOCATE functions together:
UPDATE wp_posts
SET post_content = SUBSTRING(post_content, 1, LOCATE('【Available Until】', post_content) - 1)
WHERE post_content LIKE '%【Available Until】%'
AND post_type = 'post'
AND post_status = 'publish';
This finds your marker text and keeps everything before it. The LIKE condition only hits posts that actually have the marker, so you won’t mess up other content.
Test on staging first or add LIMIT 10 to check results before running the full thing. Also try a SELECT version first to see which posts you’ll be changing.