MySQL Search and replace

control

I am trying to do I search and replace in my joomla database. I am trying to replace every instance of " " with nothing so I am using the command

UPDATE `jos_content` SET `introtext` = REPLACE(`introtext`, '<p> </p>', '')

but it doesn't return any results although I know they are there. Anyone know why?

Thanks

Weedpacket

UPDATE doesn't return any results because it's not a SELECT query.

What were you expecting to get? The number of affected records? If you're using the PDO interface that's what is returned when you use PDO::exec(). If you're using one of the extensions for a specific DBMS the interface should have an "affected_rows" function.

johanafm

And it is of course assuming that ' ' actually is in one or more fields. It's easy to get fooled by whitespace, since many different things may look just the same. I.e. ' ' and '\t', or if viewed in a browser '' and '\n' may be the same or ' ' and '\n' may be the same... And the string could actually contain '&nbps;' as well.
So if you're not looking at table data through CLI or a stand alone app, but rather a browser, wrap output inside a pre element.
Then if you still have issues, use regular expressions to deal with white spaces.

control

Weedpacket;10936944 wrote:
UPDATE doesn't return any results because it's not a SELECT query.

What were you expecting to get? The number of affected records? If you're using the PDO interface that's what is returned when you use PDO::exec(). If you're using one of the extensions for a specific DBMS the interface should have an "affected_rows" function.

So would I have to do

SELECT * FROM jos_content; UPDATE `jos_content` SET `introtext` = REPLACE(`introtext`, '<p> </p>', ''). 

[QUOTE=johanafm;10936949]And it is of course assuming that '<p> </p>' actually is in one or more fields. It's easy to get fooled by whitespace, since many different things may look just the same. I.e. '    ' and '\t', or if viewed in a browser '' and '\n' may be the same or ' ' and '\n' may be the same... And the string could actually contain '<p>&nbps;</p>' as well.
So if you're not looking at table data through CLI or a stand alone app, but rather a browser, wrap output inside a pre element.
Then if you still have issues, use regular expressions to deal with white spaces.[/QUOTE]

I am looking at this through phpmyadmin in a browser. I am not really to sure how regular expressions work, what would be the query for me to get it working?