how to do update with column with single quotes sqlserver

Question

how to do update with column with single quotes sqlserver

Navigation

#1 by (1 votes)
#2 by (1 votes)
#3 by (0 votes)

5

I need to update in a column that has a lot of single quotation marks in the field, it has been inserted content with ckeditor so it has several tags like

<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 single quotes times single quotes' / 'Times New Roman';

When I run this query in sql manager it would be to replace the single quotation marks with double quotation marks.

When I put [quote quotation marks and one more in the middle] it already error !!, the tag already underlies the sql thinks and comment after the second quotation mark the rest of the string turns gray! I've tried double-quotes and simple in the middle did not work either.

This update I need.

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

Does anyone know how to solve this?

sql-server

asked by anonymous 04.11.2015 / 02:59

3 answers

How to test an asp.net mvc controler using Moq and Entity Framework 6 How to simplify the code with ForEach's List?

score 1 · Answer 1

1

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

So the solution would look like this:

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

04.11.2015 / 12:53

score 1 · Answer 2

You can make use of the Escape Character.

It is a term for identifying a single character, within a string that changes the meaning of its successor.

As an example, we can break the line 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 quotation mark changes the result of its successor, which is also a quotation mark), so when you place a slash before it you change the successor result.

In other words, in order to avoid problems, you only have to add an escape character before it, in the case of SQL Server and in many programming languages it is "\", I have never seen another one without it.

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

score 0 · Answer 3

Keep the quotation marks simple friend, make a replace for the double quotation mark with escape in the simple way you will have no problem in SQL Server Syntax.

Example

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

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