replace serialized urls in wordpress

gistfile1.txt

################################
#
# Parse a Sql export from a Wordpress db
# and do a search and replace on URLS in
# serialised data while maintaining the 
# correct string lengths
#
# e.g. look for stuff like...
# s:76:\"http://www.website.com/wp-content/uploads/2012/04/rest_img-150x142.png\";
#
# and change to...
# s:69:\"http://www.website.com/wp-content/uploads/2012/04/rest_img-150x142.png\";
#
# Note the string changes and the number after the s: (i.e. the string length)
#
################################

# parameters
inputfilename = "C:\\projects\\www.website.com\\input.sql"
outputfilename = "C:\\projects\\www.website.com\\output.sql"
seachfor = "http://thaisquare.oi-you.com"
replacewith = "http://thaisquareserver.co.uk"
filter = /(s:)(\d+)(:\\\")(http:\/\/www.website.com\/wp-content\/uploads\/\d+\d+\/[^\\]+)(\\\";)/ # regex used to reduce the search space

# load the file content into memory
fp = File.open(inputfilename, "rb")
contents = fp.read
output_content = contents.clone # for changes while we iterate the original
fp.close

# get all of the matches 
contents.scan(filter) { | matched |
  
	# process each one
	if matched[3].include? seachfor
		
		# do the search and replace
		new_value = matched[3].gsub seachfor, replacewith
		
		# reassemble string with the replaced string and new length
		modified_string = matched[0]+new_value.length.to_s+matched[2]+new_value+matched[4]
		
		# do a search and replace in the actual content
		puts matched.join + " --> " + modified_string
		output_content.gsub! matched.join, modified_string
		
	end
	
	
}


# save the output
aFile = File.new(outputfilename, "w")
aFile.write(output_content)
aFile.close