t-sql escape problem

Question

i can not delete this lines from DB becouse of Unclosed quotation marks. How cn i escape it. I try with backslash but not working.

When i try to delete Delete from dbo.Cities where name = 'Àrbatax'; i get (0 row(s) affected) even in DB 12 rows exist. Problem with unrecognized char À

Delete from dbo.Cities where name = 'Ra's al Khaymah';
Delete from dbo.Cities where name = 'Cala de s'Algar';
Delete from dbo.Cities where name = 'Monte Sant'Angelo';
Delete from dbo.Cities where name = 'San Pawl il-Baħar';
Delete from dbo.Cities where name = 'Santa Eulària des Riu';
Delete from dbo.Cities where name = 'São Luís';
Delete from dbo.Cities where name = 'Platja d'Aro';
Delete from dbo.Cities where name = 'Cefalù';
Delete from dbo.Cities where name = 'Lun-Pequeño';
Delete from dbo.Cities where name = 'Àrbatax';
Delete from dbo.Cities where name = 'Breña Baja';

Answer 1

For single-quotes I believe you can double the quotes:

Delete from dbo.Cities where name = 'Ra''s al Khaymah'

I'm not sure about the other characters.

Answer 2

Escape single quotes in T-SQL by doubling them:

Delete from dbo.Cities where name = 'Ra''s al Khaymah';

Answer 3

One possible explanation that might cause the delete to not work is because there are special characters in your where clause and you have a single quote inside a string literal that is delimited by single quotes.

1. Step one would be to check out what is the datatype of the columns where you have special characters. If those columns are of type char or varchar you must change them to nchar or nvarchar. The reason behind this is that char and varchar does not support unicode characters (in other words the special characters).

   Information on nchar and nvarchar:

   • http://msdn.microsoft.com/en-us/library/ms186939.aspx
   • http://searchsqlserver.techtarget.com/tip/Differences-between-varchar-and-nvarchar-in-SQL-Server

2. Step two would be to modify the code you are using to delete data. In order to have a single quote inside the string literal for the comparison in the where clause you must write two single quotes. Example: Delete from dbo.Cities where name = 'Ra''s al Khaymah';.

I hope this helps!

Answer 4

If you have strings with non-ASCII characters, you need to use the Unicode quotes N'':

Delete from dbo.Cities where name = N'Àrbatax';