Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2003
    Posts
    2

    Unanswered: Easy (?) data-editing solution? Need help pls.

    I have several thousand rows of (text) data that have some incorrect pieces. I need a way to delete part of the data but leave the rest intact.

    Example:

    Current data: "Bloomfield, CT"

    Needs to become: Bloomfield

    In other words, I need to remove the left quote, and everything after (including) the comma.

    there are dozens of different cities in the DB like this.

    I can write the simple query that can pull out all of the data that has a comma, or quotes. What I don't seem to get is how to then "erase" the quotes (or the string that includes the comma and everything after it) and then update the DB with this new value.

    Help?

  2. #2
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106

    Re: Easy (?) data-editing solution? Need help pls.

    Most databases have a substring and in-string (or position string) function which you can use in combination to do parsing. My example is oriented to DB2:

    SUBSTR(current_data, 2, POSSTR(current_data, ',') - 1)
    Result would be Bloomfield

    You can use the above in an UPDATE statement:
    UPDATE table SET city = SUBSTR(current_data, 2, POSSTR(current_data, ',') - 1)

    Originally posted by rexnervous
    I have several thousand rows of (text) data that have some incorrect pieces. I need a way to delete part of the data but leave the rest intact.

    Example:

    Current data: "Bloomfield, CT"

    Needs to become: Bloomfield

    In other words, I need to remove the left quote, and everything after (including) the comma.

    there are dozens of different cities in the DB like this.

    I can write the simple query that can pull out all of the data that has a comma, or quotes. What I don't seem to get is how to then "erase" the quotes (or the string that includes the comma and everything after it) and then update the DB with this new value.

    Help?

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Wink

    Also, use the 'REPLACE' and 'GRATER' functions to eliminate the quotes:

    UPDATE mytable
    SET city = REPLACE(SUBSTR(city, 1
    ,GREATER(POSSTR(city, ',') - 1,LENGTH(city)))
    ,'"','')
    WHERE POSSTR(city, ',') > 0
    OR POSSTR(city, '"') > 0

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    Aug 2003
    Posts
    2
    Thanks you both, will give it a shot. Unfortunately, I'm using MS Access and it doesn't recognize those particular functions, but I think I can replace them.

  5. #5
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106
    For Access take a look at Instr and Mid there is even a Replace. Have fun!

    Originally posted by rexnervous
    Thanks you both, will give it a shot. Unfortunately, I'm using MS Access and it doesn't recognize those particular functions, but I think I can replace them.

Posting Permissions

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