Changing a single character within fields in a table?

• mick burden

  SSCarpal Tunnel

  Points: 4432

  More actions

  August 29, 2006 at 5:40 am

  #173922

  Can anyone help me with a problem I have, I have a table in a SQL2000 sever database, the table has 4 fields which has at least 2000 records. I've discovered there is a character in some records that needs to be changed. Is there a way of writing a query to do it for me?

  as an example one field has "Rock & Roll", I'd like to be able to change the "&" for another character

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  August 29, 2006 at 6:24 am

  #657249

  Mick

  You don't say whether it's the same character that needs changing in all places, or if so whether all occurrences of that character should be changed.  However, a good place to start would be to look up the REPLACE function.

  John

• mick burden

  SSCarpal Tunnel

  Points: 4432

  More actions

  August 29, 2006 at 6:27 am

  #657250

  I need to replace all instances of the character within all the records

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  August 29, 2006 at 6:40 am

  #657252

  Assuming you want to replace every '&' with a '+':

  UPDATE MyTable SET

    col1 = REPLACE(col1, '&', '+'),

    col2 = REPLACE(col2, '&', '+'),

    col3 = REPLACE(col3, '&', '+'),

    col4 = REPLACE(col4, '&', '+')

  John

• mick burden

  SSCarpal Tunnel

  Points: 4432

  More actions

  August 29, 2006 at 6:50 am

  #657257

  Thanks John, that worked a treat