Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2003
    Posts
    3

    Unanswered: How to remove 2 characters in one column for all records

    Hi

    Does anyone know a quick way of removing 2 characters from one column in all records of a MySQL table?

    The characters in question are "-C" and appear at the end of a VARCHAR field. There are just over 2,000 records.

    I know how to do this using PHP but was wondering if there was a quicker way using MySQL commands. e.g. UPDATE table SET ....

    Thanks

  2. #2
    Join Date
    Jun 2003
    Posts
    21

    Re: How to remove 2 characters in one column for all records

    Originally posted by HungryH
    Hi

    Does anyone know a quick way of removing 2 characters from one column in all records of a MySQL table?

    The characters in question are "-C" and appear at the end of a VARCHAR field. There are just over 2,000 records.

    I know how to do this using PHP but was wondering if there was a quicker way using MySQL commands. e.g. UPDATE table SET ....

    Thanks
    UPDATE table_name SET field_to_change = LEFT(field_to_change, LENGTH(field_to_change)-2)

  3. #3
    Join Date
    Jun 2003
    Posts
    3
    Thanks very much.

    That has saved me a lot of time.

    Do you know if you can substitute strings in an update as well? I know you can use SUBSTR in a SELECT but it doesn't seem to work with updates.

  4. #4
    Join Date
    Jun 2003
    Posts
    21
    Originally posted by HungryH
    Thanks very much.

    That has saved me a lot of time.

    Do you know if you can substitute strings in an update as well? I know you can use SUBSTR in a SELECT but it doesn't seem to work with updates.
    Same idea:

    UPDATE table_name set field_to_change = substring(field_to_change, 1, 10)

    replaces field_to_change with the first 10 chars of field_to_change for each record. Always try to use SQL is you need to change every record in the same way. It's much faster and easier then using PHP or some other form of script.

    all the string functions are documented rather well on the MySQL site: http://www.mysql.com/doc/en/String_functions.html

    Grtz,

    Niels

  5. #5
    Join Date
    Jun 2003
    Posts
    3
    Thanks again

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •