OK, a guy has a website with a price field. Unforunately this is set as varchar and many numbers are in the following format.

1000 EUR

I need to remove the EUR part.

Can this be done with MySQL? Or do I need to use PHP to analyse the string?

if it's always in the format 1000 eur or ( nnnn ccc) where n= number and c= currency, you can use

The price varies. Some prices do not have EUR.

Unfortunately there isn't a way to select part of a column using regular expressions. You can select a whole column based on a regex and check a regex in the select column list but not actually pull part of that regex out. SQL is quite a simple language so doesn't really have a function to achieve that sort of thing.

If I were dealing with this database, I would write a php script that pulls all of the prices out and their tables primary key, runs a preg_replace over all the prices to filter them down to their numeric values, alters the table price column to be integer or double and then updates the records with the primary key and updated value.

You can use MySQL's REPLACE() string function...

SELECT TRIM(REPLACE(`price`,'EUR','')) as `newPrice` FROM `table`

The TRIM() function will get rid of any spaces.

Rich

If this works... I will be very happy indeed. I need to do this over the weekend and meh, I want to relax and watch movies. Not work.

And if that doesn't work, maybe you export the data to a csv and edit it there. Then import it again. Or maybe create some nice update strings using the concat function of excel. Anyway, i'd say the SQL thing should do the trick.

Good luck