• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 439
  • Last Modified:

Find and replace part of a string

Experts-

I have string responses stored in my table.  Some of the responses contain "dbmSnippets" within the response string.  I would like to find all of the dbmSnippets and replace them with \' keeping the rest of the response in tact.  Any suggestion on how to do this would be appreciated.

Thanks
Nick
0
nmarano
Asked:
nmarano
  • 5
  • 4
  • 3
2 Solutions
 
NetExpert-WarszawaCommented:
UPDATE tablename SET response = REPLACE(response, 'dbmSnippets', '\') WHERE response LIKE '%dbmSnippets%';
0
 
nmaranoAuthor Commented:
Thanks Net.

 Will that keep the rest of my string intact?  So if my string looks like,
Yes~I agree that JuliadbmSnippetss quadlateral performance"  Will it simply replace the dbmSnippets to look like...
"Yes~I agree that Julia's quadlateral performance"
0
 
Kevin CrossChief Technology OfficerCommented:
It looks like the current query is replacing it with a slash '\' -- update the code to be REPLACE(response, 'dbmSnippets', '\'') and it will result in only the dbmSnippets being replaced. The rest of the string will be untouched.
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
NetExpert-WarszawaCommented:
No, it will not. I have thought you want \, not '.

You need:
UPDATE tablename SET response = REPLACE(response, 'dbmSnippets', "'") WHERE response LIKE '%dbmSnippets%';

WHERE response LIKE '%dbmSnippets%' will look for all rows for which a column response contains dbmSnippets.

REPLACE(response, 'dbmSnippets', "'") will produce a string that will be a content of response with all dbmSnippets replaced with '.

Before you run it over your production table, create a test table and try the query on it.
0
 
nmaranoAuthor Commented:
Sorry net correct, I want /'
0
 
NetExpert-WarszawaCommented:
Just put what you want as the third parameter for REPLACE.
0
 
nmaranoAuthor Commented:
Net, I'm looking for a slash and an apostrophe so the replace will be /'

Thanks
nick
0
 
NetExpert-WarszawaCommented:
You are not limited to a single character. Try:

UPDATE tablename SET response = REPLACE(response, 'dbmSnippets', "/'") WHERE response LIKE '%dbmSnippets%';
or
UPDATE tablename SET response = REPLACE(response, 'dbmSnippets', '/\'') WHERE response LIKE '%dbmSnippets%';
0
 
nmaranoAuthor Commented:
Hey Net,

Sorry for the confusion.  So, I'm looking to use \' and not the back slash/.  Now I tested it on a couple of results, but it's taking the dbmSnippets out but only replacing it with a ' instead of \'....

0
 
Kevin CrossChief Technology OfficerCommented:
Here is a reference on escaping special characters in MySQL:
http://dev.mysql.com/doc/refman/5.0/en/string-syntax.html

Manual on REPLACE:
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_replace

What you want is:
REPLACE(response, 'dbmSnippets', '\\\'')

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
Please credit, Net, by the way. Just helping you get the understanding you need.
0
 
nmaranoAuthor Commented:
thank you guys for your help.  Truly appreciated.  Net, thanks for the query, and mwvisa, thanks for the links.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 5
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now