Changing MySQL field entries using a wildcard

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
besbajah
Forum Newbie
Posts: 23
Joined: Fri May 20, 2005 1:00 pm
Location: Brazil

Changing MySQL field entries using a wildcard

Post 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.
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post by hawleyjr »

Try This:

Code: Select all

UPDATE table SET fieldx like '%.php' WHERE fieldx like '%.htm'
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post by hawleyjr »

Oops. on your set use a substring function from mysql string functions:

http://dev.mysql.com/doc/mysql/en/string-functions.html
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Code: Select all

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