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.

Easiest way to update lots of records

I have a database where several thousand records have NULL in a binary field. I want to change all the NULLs to false. I have Visual Studio 5, and the database is a SQL Server 5 database on a remote server. What is the easiest way to do this? Is there a query I can run that will set all ReNew to false where ReNew is Null? This is a live database so I want to get it right. I can't afford to mess it up.

Diane

You can do something like the following:

UPDATE [Table] SET ReNew=0 WHERE ReNew IS NULL

Before I run an update or delete statement, I always run it as a select first, just to make sure I'm only updating the rows I want. Something like this:

SELECT * FROM [Table] WHERE ReNew IS NULL

|||

Benners_J:

Before I run an update or delete statement, I always run it as a select first, just to make sure I'm only updating the rows I want. Something like this:

SELECT * FROM [Table] WHERE ReNew IS NULL

Rather, SELECT COUNT(*) FROM [Table] WHERE ReNew IS NULL would be good, since the poster is saying there are too many rows. Anything which makes you feel comfortable.