[SOLVED] Urgent (but easy!): Find/replace in SQL?

whuppins

OK, I'm not very good with SQL, and I'm sure there are a ton of tutorials, etc. out there for this sort of thing, but I have a very real problem that I need to solve tonight, so I just need a quick answer.

I've identified several hundred rows in my database that each contain incorrect info for one particular field. This field takes the form of a large string of text, and its value varies from row to row. In each of the problem rows, one part of that string is supposed to be (let's say) "apple" but instead is "banana". So all I need to do is find all instances of "banana" in that field for all the records in my selection and replace it with "apple", leaving the rest of the text intact.

What's the syntax for this?

Thanks for the help!

tralev

If Ctrl-H doesn't work I'd cut and paste it into Notepad and do the replace there.

whuppins

That's not a helpful answer. I'm talking about 900+ rows in an Oracle database, not a single text file.

mastman

edit, wait, you want to change a substring

Sevorak

Since you want to change a substring and not the entire string in the column, this actually isn't very easy and I'm pretty sure it's impossible to do with pure SQL. You're going to need to either write a program using JDBC or something similar or write a PL/SQL procedure using cursors. Both are essentially the same thing, and which one you choose will depend on whether you're more comfortable with java and the like or PL/SQL.

Basically you need to get all the rows with "banana" in their string into a cursor or similar structure, then iterate through them and update each one setting the part of the string to "apple".

I don't have time to write the programs since I'm at work, but http://www.jdbc-tutorial.com/ or http://www.oracle.com/technology/documentation/berkeley-db/db/gsg/CXX/Cursors.html should help.

foggrat

Oracle has a transact sql replace function also, try this link:

http://searchoracle.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid41_gci1321496,00.html#

Or just google "Oracle SQL Replace".

edit:

I suppose, upon carefully reading the article, he doesn't explicitly state oracle supports this, despite the fact it's an "oracle website". If you need to cheese it, here's a thought, although I'm from a MS-SQL background, so your syntax may vary. Imagine your table structure is like this:


MyTable
=======
PrimaryKey
FieldWithStuffThatNeedsReplaced
etc

You could run this query:

select 'Update MyTable set FieldWithStuffThatNeedsReplaced = ''' + FieldWithStuffThatNeedsReplaced + ''' where PrimaryKey = ' + PrimaryKey from MyTable

This should get you a lot of rows returned in the format:

Update MyTable set FieldWithStuffThatNeedsReplaced = 'Susan loves apples' where PrimaryKey = 1
Update MyTable set FieldWithStuffThatNeedsReplaced = 'Anthony loves apples' where PrimaryKey = 2

You could then do the find/replace in notepad, and execute the queries. Obviously, this is far from desirable, but if you work in the kind of barbaric database that won't allow you to Replace(), it may help.

whuppins

foggrat wrote: »

Oracle has a transact sql replace function also, try this link:

http://searchoracle.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid41_gci1321496,00.html#

Or just google "Oracle SQL Replace".

edit:

I suppose, upon carefully reading the article, he doesn't explicitly state oracle supports this, despite the fact it's an "oracle website". If you need to cheese it, here's a thought, although I'm from a MS-SQL background, so your syntax may vary. Imagine your table structure is like this:


MyTable
=======
PrimaryKey
FieldWithStuffThatNeedsReplaced
etc

You could run this query:

select 'Update MyTable set FieldWithStuffThatNeedsReplaced = ''' + FieldWithStuffThatNeedsReplaced + ''' where PrimaryKey = ' + PrimaryKey from MyTable

This should get you a lot of rows returned in the format:

Update MyTable set FieldWithStuffThatNeedsReplaced = 'Susan loves apples' where PrimaryKey = 1
Update MyTable set FieldWithStuffThatNeedsReplaced = 'Anthony loves apples' where PrimaryKey = 2

You could then do the find/replace in notepad, and execute the queries. Obviously, this is far from desirable, but if you work in the kind of barbaric database that won't allow you to Replace(), it may help.

Two good pieces of info, and I would have settled for doing the second one, but the first one works like a charm! Thanks so much for your help; you may very well have saved me from having to pull an all-nighter.