Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
melcraig
Starting Member
39 Posts |
 Posted - 2006-04-06 : 17:30:35
|
| I have a table(Course) with a column(Description) that is varchar, that users(who have smart quotes turned on) cut and pasted from MS Word that have the single quote ’ and I need to replace all of them with an apostrophy 'I can run the script to show all of them Select CourseID, Description from Course where Description LIKE '%[’]%' and they show up but how do I go about replacing them?Thanks, any help will be much appreciated.Melinda |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2006-04-06 : 17:39:22
|
| UPDATE CourseSET Description = REPLACE(Description, '’', '''')---------------------------EmeraldCityDomains.com |
 |
|
|
melcraig
Starting Member
39 Posts |
Posted - 2006-04-06 : 17:52:32
|
| Thank you!I was trying to replace with an apostropy like this ''' and it kept refusing it. I thought '''' would replace it with two.Thanks again!Melinda |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2006-04-06 : 18:07:03
|
| The reason it doesn't is that the apostrophe has to be "escaped" by doubling it. With only 3 you end up with an open quote.---------------------------EmeraldCityDomains.com |
|
|
|
melcraig
Starting Member
39 Posts |
Posted - 2006-04-06 : 18:17:26
|
| Thanks for the explanation! Melinda |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-07 : 06:54:06
|
| You might want to do something like:UPDATE CourseSET Description = REPLACE(Description, '’', '''')WHERE Description LIKE '%’%'to reduce the number of rows that get "hit". Won't matter if there are not a huge number of rows in your Table, but if there are it will reduce the effort on the server.Note also that you can "nest" the REPLACE to deal with both left & right quotes in a single strike:UPDATE CourseSET Description = REPLACE(REPLACE(Description, '‘', ''''), '’', '''')WHERE Description LIKE '%‘%' OR Description LIKE '%’%'Kristen |
|
|
|
|
|
|
|
|
MS Word Curly Quotes