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?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?|||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

:cool:|||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.|||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.