Updating Mysql Database using Regex

I have complex find and replace problem..

[grnidone]

I have a complex find and replace issue in a Mysql database, and I need to use regex to find the pattern.

What I have been using is an update command with replace() in the end of it:

UPDATE tablename SET column_name = REPLACE ( `column_name` ,
' text_to_look_for',
' ' );

This will find particular text and then replace it with a space. Then, I trim the column later. The kicker is, I can't use regex with it, so I have to look for the specific text.

Is there a way to use eregi_replace to update a mysql database?

[jatar_k]

what about something like this
[mysql.com...]

[coopster]

Not sure on this, but it seems you are using the REPLACE function in the UPDATE statement rather than on the WHERE clause, try this...

UPDATE tablename SET column_name = ' ' WHERE column_name =
'text_to_look_for';

Of course, you would replace the 'text_to_look_for' with your regular expression.

[grnidone]

Sorry about that, I wasn't clear.

I have no problems making the proper regex patterns, but I can't figure a way to use a regex pattern in the Update command such that it will update only the text I want to remove.

Like this:

BEFORE update:
Column
----------------
blat567 This is the column data text.
blat8975 More column text here
blatbla Still more text.

AFTER Update:
Column
----------------
This is the column data text.
More column text here
Still more text.

Any more suggestions?

[panic]

Hrrrm... you might want to try and do that in PHP using preg_match ( [php.net...] ) .

Split the string by spaces, and for each word that contains "blat", remove that word from the string using ereg_replace ( [php.net...] ).

-panic

[timster]

If you are hoping PHP can make MySQL understand regex, I think you're out of luck. But if your example "for real" it's not so bad:

Write a PHP script that:

1. Fetch every row in MySQL where fieldInQuestion='bla%'
(MySQL can't to regex, but this ID's the bad fields.)

2. Loop through the rows:
- Use regex to correct the value
- If the regex succeeded (which it probably will) update the row in MySQL

Does that make sense?

[grnidone]

Yeah, that's exactly what I want to do, Timster.

My stumbling block is *how* to make that happen. I can't figure out why this, for example won't work: (Sorry for posting code..)

1. Select the ID column and messed up column. The ID column will be used to update only that row later...

$result = mysql_query("Select ID, MessedUpField from TableName");

2. Then I walk through the results one at a time and set variables to each separate field:

while ($row = mysql_fetch_array($result)) {
$Num = $row["ID"];
$Field = $row["MessedUpField"];

3. Then I look for the regex pattern and update the field to remove the pattern found.

$Field = eregi_replace("RegExCodeHere","",$Field);

$sql = mysql_query("Update TableName set MessedUpField = $Field where ID = $Num");
}

I am sure I am either making a simple error OR that I am using the wrong command altogether. I just can't figure out which.

[jatar_k]

post the actual code that is giving you problems. Maybe that will help us see it better. Your explanation sounds right on the money so it may just be a simple little thing.

[grnidone]

The regex I am using in the eregi_replace is:

$Field = eregi_replace("^\\[.+\\]","",$Field);

The regex part translates to:
"Any number of characters between a [ (left bracket) and a ] (right bracket) at beginning of line."

[hpche]

Just a simple thing, but wouldn't you need quotes round the $Field variable in this line :

$sql = mysql_query("Update TableName set MessedUpField = $Field where ID = $Num");

Or, another idea, maybe on some line you used $field instead of $Field?

Otherwise, how far does it get and what error message is it giving you?

[grnidone]

Just a simple thing, but wouldn't you need quotes round the $Field variable in this line :

AHA! That fixed it. Always the little things....