?
Solved

Find and replace part of a string

Posted on 2011-09-05
12
Medium Priority
?
435 Views
Last Modified: 2012-05-12
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
Comment
Question by:nmarano
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
12 Comments
 
LVL 10

Accepted Solution

by:
NetExpert-Warszawa earned 1600 total points
ID: 36485289
UPDATE tablename SET response = REPLACE(response, 'dbmSnippets', '\') WHERE response LIKE '%dbmSnippets%';
0
 
LVL 2

Author Comment

by:nmarano
ID: 36485343
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36485387
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 10

Expert Comment

by:NetExpert-Warszawa
ID: 36485391
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
 
LVL 2

Author Comment

by:nmarano
ID: 36485573
Sorry net correct, I want /'
0
 
LVL 10

Expert Comment

by:NetExpert-Warszawa
ID: 36485585
Just put what you want as the third parameter for REPLACE.
0
 
LVL 2

Author Comment

by:nmarano
ID: 36485594
Net, I'm looking for a slash and an apostrophe so the replace will be /'

Thanks
nick
0
 
LVL 10

Expert Comment

by:NetExpert-Warszawa
ID: 36485610
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
 
LVL 2

Author Comment

by:nmarano
ID: 36485718
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
 
LVL 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 400 total points
ID: 36485757
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36485764
Please credit, Net, by the way. Just helping you get the understanding you need.
0
 
LVL 2

Author Comment

by:nmarano
ID: 36485813
thank you guys for your help.  Truly appreciated.  Net, thanks for the query, and mwvisa, thanks for the links.
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question