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

How to construct SQL Search and Replace query

I need a sql query that will search and replace the following string of text inside a field in my database table.  I want to replace the string ' with the single quote character.  Right now, I have a bunch of LastName fields that look like this:

D'Antonio
O'Block
O'Brien
O'Donnell
O'Flaherty

Again, I just want to get rid of that HTML equivalent and use the literal single quote character instead.

TIA for assistance in constructing this query!
0
dstjohnjr
Asked:
dstjohnjr
  • 2
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
UPDATE your_table_name
SET your_field_name = REPLACE(your_field_name, ''', '''')
WHERE your_field_name LIKE '%'%'
0
 
usachrisk1983Commented:
If REPLACE is still a valid function in MSSQL 2005 then this should work.  Backup your table first or try in test!

update tblName
   set lastName = Replace(lastName, "&#39", "'")
 where lastName like '%#39#%';

Open in new window

0
 
dstjohnjrAuthor Commented:
Had to give full points to this solution since A) it came in first, B) was the full working solution and C) the other solution did not work due to double quotes being used and not single quotes in the replace statement.  Thanks for expert assistance!
0
 
Kevin CrossChief Technology OfficerCommented:
You are most welcome.

Glad that helped.

Regards,
Kevin
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now