Page 1 of 1

Changing MySQL field entries using a wildcard

Posted: Sun Jul 24, 2005 11:18 am
by besbajah
Hi there,

I need to update about 500 or more rows in a table.

Specifically I need to change the file extension from .htm to .php in a specific field but am not sure how to do it.

Is there an update command that can wildcard this? eg,

Code: Select all

UPDATE table SET fieldx like '*.php' WHERE fieldx like '*.htm'
or if that's not the right way to wildcard, what is?

Cheers,
Bes.

Posted: Sun Jul 24, 2005 12:17 pm
by hawleyjr
Try This:

Code: Select all

UPDATE table SET fieldx like '%.php' WHERE fieldx like '%.htm'

Posted: Sun Jul 24, 2005 12:18 pm
by hawleyjr
Oops. on your set use a substring function from mysql string functions:

http://dev.mysql.com/doc/mysql/en/string-functions.html

Posted: Sun Jul 24, 2005 7:14 pm
by timvw

Code: Select all

UPDATE table
SET column=CONCAT(TRIM(TRAILING '.htm' FROM column), '.php')
WHERE column LIKE '%.htm'