MySQL: Find and Replace Between Certain Characters

In field post_content I have a string like this in nearly 800 rows:

http://somesite.com/">This is some site</a>

I need to remove everything from "> onwards so that it leaves just the URL. I can't do a 开发者_如何学编程straight find and replace because the text is unique.

Any clues? This is really my first foray into MySQL database modifications but I did do an extensive search before posting here.

Thanks,

~Kyle~

From this site: http://www.regular-expressions.info/mysql.html

LIB_MYSQLUDF_PREG

If you want more regular expression power in your database, you can consider using LIB_MYSQLUDF_PREG. This is an open source library of MySQL user functions that imports the PCRE library. LIB_MYSQLUDF_PREG is delivered in source code form only. To use it, you'll need to be able to compile it and install it into your MySQL server. Installing this library does not change MySQL's built-in regex support in any way. It merely makes the following additional functions available:

Here it comes...

PREG_CAPTURE extracts a regex match from a string. PREG_POSITION returns the position at which a regular expression matches a string. PREG_REPLACE performs a search-and-replace on a string. PREG_RLIKE tests whether a regex matches a string.

Sounds exactly what you're looking for.

All these functions take a regular expression as their first parameter. This regular expression must be formatted like a Perl regular expression operator. E.g. to test if regex matches the subject case insensitively, you'd use the MySQL code PREG_RLIKE('/regex/i', subject). This is similar to PHP's preg functions, which also require the extra // delimiters for regular expressions inside the PHP string.

See this post: How to do a regular expression replace in MySQL?

Either that or you could just write a script in any lanugage which goes through each record, does a regex replacement and then updates the field. For more info on regex, see here: http://www.regular-expressions.info/reference.html

There's a number of options. One might be to use SUBSTRING_INDEX():

UPDATE
    table
SET field = SUBSTRING_INDEX( field, '">', 1 )

It's possible - there is a syntax for User Defined Functions which would let you pass in a regular expression pattern that matches the link and strips everything else.

However, this is quite complicated for somebody new to MySQL, and from your question, this sounds like a one-off. In which case - why not just use Excel and then reimport the data?

Great stuff!

All seems doable with a little bit of time and self education.

In the end, I exported that table as a CSV in Sequel Pro and did some nifty find and replace work in Coda. Not as sophisticated as your suggestions, but it worked.

Thanks again, ~Kyle~