Need a search -> replace query in MYSQL - Get a green!

Discussion in 'MySQL' started by aaron_nimocks, Jun 12, 2007.

0
  1. #1
    I have a database that lists all States in the US. It lists them by the two letter designation though. Like AL instead of Alabama. Now I want to change that in phpmyadmin so Im looking for a query that would work.

    The table name is "default_table"
    The field name is "state"

    So basically replace all AL in field state with Alabama.

    If you can provide me with the exact query needed Id appreciate it! :)
     
    aaron_nimocks, Jun 12, 2007 IP
  2. Clark Kent

    Clark Kent Guest

    Messages:
    122
    Likes Received:
    11
    Best Answers:
    0
    Trophy Points:
    0
    #2
    update default_table set state=replace(state, 'AL','Alabama') where state='AL'
     
    Clark Kent, Jun 13, 2007 IP
    aaron_nimocks likes this.
  3. jamix

    jamix Peon

    Messages:
    30
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #3
    You can do it easier if the abbreviated state name is all there is in the "state" field. Then no replace() is necessary, just do

    update default_table set state='Alabama' where state='AL';
     
    jamix, Jun 13, 2007 IP
    aaron_nimocks likes this.