how to update column with single quote sqlserver

Question

I’m needing to update a column that has lots of simple quotes in the field, content with the letter was inserted so it has several tags of the type

<span style="FONT-SIZE: 12pt; FONT-FAMILY: &quot;Times New Roman&quot;,&quot;serif&quot;; mso-fareast-font-family: 'Times New Roman'; mso-ansi-language: PT-BR; mso-fareast-language: PT-BR; mso-bidi-language: AR-SA"><font face="Arial" size="2">meu conteudo </font></span>

The problem is in simple quote times' / 'Times New Roman';

when I run this query in sql manager it would be to replace single quotes with double quotes.

When I put [quotation marks and one more in the middle] it’s already wrong!! , the tag is already underlined sql thinks that and comment after the second quote the rest of the string turns gray! I’ve tried double quotes and single quotes in the middle also didn’t work.

This update I need.

UPDATE pessoas SET nome = replace(nome, ''', '"') WHERE nome LIKE '%'%';

Someone knows how I fix it?

Answer 1

You can make use of the Escape Character.

It is a term to identify a unique character within a string that changes the meaning of its successor.

We can put as an example the line break in text files "n" is a common character like any other but when added n its meaning changes "in programming". n in a string is the line break.

Solving the problem...

'->'(this quote changes the result of its successor which is also a quote)', so when you put a bar before it you change the result of its successor.

that is to avoid problems, you only need to add one escape character before it, in the case of SQL Server and in many programming languages is the " ", never seen another without this being.

Example with escape character ' \' ', applying in your case would look like this UPDATE pessoas SET nome = replace(nome, '\'', '"') WHERE nome LIKE '%\'%';

Answer 2

In Sql-Server you "escape" the simple quotes by duplicating it.

Then the solution would be like this:

UPDATE pessoas SET nome = replace(nome, '''', '"') WHERE nome LIKE '%''%';

Answer 3

Keep the quotes simple friend, make a replace for the double quotes with escape in simple so you will not have problem in SQL-Server Syntax.

Example

UPDATE pessoas SET nome = replace(nome, '\'', '"') WHERE nome LIKE '%\'%';

But in your case will give problem in your HTML. Because the 'Times New Roman'; is within the attribute style then in the end you’ll be ... style="font-family: "Times New Roman";"