Update mysql table with a regular expression

I have table called "location" with a field "area". This field can contain various type of values. Example:

"1.0/5.", "32.22/29.", ".0/2.", "6.4/15.", "nothing" etc

I wan开发者_开发百科t create a regular expression that selectes only the the first number from this field and adds 1 to it.

So using the example above, the update will result in:

"2", "33", "1", "7".

Hopefully that makes sense.

Try this select query -

SELECT TRUNCATE(SUBSTRING_INDEX(area, '/', 1), 0) + 1 FROM location

Then rewrite it to UPDATE statement.

1 + CAST(LEFT(area, LOCATE('/', area) - 1 ) AS SIGNED INTEGER)

or

1 + CAST(LEFT(area, LOCATE('/', area) - 1 ) AS DECIMAL)

Both will return same result (integer).

@jamester: Did you try?:

SELECT 1 + CAST(LEFT(area, LOCATE('/', area) - 1 ) AS DECIMAL)
       AS result
FROM location

Try this:

preg_replace("/^[^0-9]*([0-9]+).*$/e","$1+1", $input);

.. unless ofcourse you want to do it straight in the MySQL query