SQL Server Find and Replace Script
September 17, 2007 7:20 AM Subscribe
SQL SERVER - I'm not a SQL script guy, but I've got a SQL script problem.
I'm trying to do a simply find-and-replace of a url wherever it may be in the db. The script I was given doesn't work, and the guy who wrote the script is gone.
You can see the script here: http://number9media.com/scripts/FindAndReplace-qlinks.sql
When I run it, I get no errors, but it doesn't make any changes. Someone pointed to the PATindex in line 22 as a potential problem, if that helps.
I'm trying to do a simply find-and-replace of a url wherever it may be in the db. The script I was given doesn't work, and the guy who wrote the script is gone.
You can see the script here: http://number9media.com/scripts/FindAndReplace-qlinks.sql
When I run it, I get no errors, but it doesn't make any changes. Someone pointed to the PATindex in line 22 as a potential problem, if that helps.
Response by poster: But the string may be embedded within the data field. If my original string is 'John' and my replacement is 'Bob', what happens when script encounters 'The boy's name is John'?
Assuming it recognizes the substring 'John', won't it replace the whole thing with 'Bob'?
posted by jpoulos at 7:36 AM on September 17, 2007
Assuming it recognizes the substring 'John', won't it replace the whole thing with 'Bob'?
posted by jpoulos at 7:36 AM on September 17, 2007
I agree that the script is completely over-written, but I think the main problem is that the @otxt and @ntxt parameters are blank, which means that you're effectively replacing nothing with nothing. Have you re-set those parameters correctly?
posted by mkultra at 7:38 AM on September 17, 2007
posted by mkultra at 7:38 AM on September 17, 2007
Nope, you'll get "The boy's name is Bob". The only caveat is that if you only want to replace the first occurrance of the string in the column, you'll need to do some more acrobatics, but you certainly don't need to open a cursor to do it.
(If you want to see this in action, try it yourself:
posted by uncleozzy at 7:39 AM on September 17, 2007
(If you want to see this in action, try it yourself:
SELECT REPLACE('His name is John', 'John', 'Bob')posted by uncleozzy at 7:39 AM on September 17, 2007
Response by poster: Oops. The version I posted was the original that they sent me. The one I ran DOES have values for @otxt and @ntxt. I've updated the one online. http://number9media.com/scripts/FindAndReplace-qlinks.sql
posted by jpoulos at 7:41 AM on September 17, 2007
posted by jpoulos at 7:41 AM on September 17, 2007
Response by poster: uncleozzy's code worked. Thank you! You just put an end to a week-long nightmare.
posted by jpoulos at 8:14 AM on September 17, 2007
posted by jpoulos at 8:14 AM on September 17, 2007
This thread is closed to new comments.
UPDATE library
SET filename = REPLACE(filename, 'original string', 'replacement string')
WHERE libtype_id = 4
posted by uncleozzy at 7:32 AM on September 17, 2007