SQL server - I am using an SQL file called world.SQL. It has an accented column holding data like 'Mas d\'Alins' ' - how do i insert this?

Question

This how I am defining it:

USE [ShopEarthDB]
GO
 ---DROP TABLE IF EXISTS SE_Cities
IF OBJECT_ID('SE_Cities') IS NOT NULL  
DROP TABLE SE_Cities 

CREATE TABLE [dbo].[SE_Cities](
[id] [int] IDENTITY(1,1) NOT NULL,
[region_id] [int] NOT NULL,
[country_id] [smallint] NOT NULL,
[latitude] [decimal](10, 8) NOT NULL,
[longitude] [decimal](11, 8) NOT NULL,
[name] [NVARCHAR](255)NOT NULL,
PRIMARY KEY CLUSTERED 
(
[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE INDEX SE_Cities_Index
ON SE_Cities (country_id,region_id,name);
GO

SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT SE_Cities ON
INSERT INTO SE_Cities 
(id,
region_id,
country_id,
latitude,
longitude,
name )
VALUES
(1, 1, 1, 42.48333330, 1.46666670, 'Aixàs'),    
(61, 1, 1, 42.43333330, 1.45000000, **'Mas d\'Alins''**), 
enter code here
 -- the following 3 are commented out, but also cause issues 
 --(143, 9, 2, 24.29861110, 53.20916670, 'Al Fuyay\''),
 --(156, 9, 2, 22.96666670, 54.30000000, 'Al Hama\'im'), 
 --(739, 8, 2, 25.18222220, 56.22833330, 'Sa'if'),

(739, 8, 2, 25.18222220, 56.22833330, 'Sa'if');
------
-----------------

The error I receive is:

Msg 102, Level 15, State 1, Line 26 Incorrect syntax near 'Alins'.

I am trying to insert these the right way. I think its collation that I am looking for. Thanks for your time and help.

Use a second apostrophe to escape the first, a la `'Sa''if'` — Andy, Nov 20 '18 at 20:46
you have to escape single quotes in your string data with another quote. Example: This - (61, 1, 1, 42.43333330, 1.45000000, 'Mas d\'Alins') isn't valid, it should be (61, 1, 1, 42.43333330, 1.45000000, 'Mas d\''Alins') — Tim Mylott, Nov 20 '18 at 20:48
Open your file and add another apostrophe to escape it, run it and save the changes. done :) — Ilyes, Nov 20 '18 at 21:23
Possible duplicate of [How do I escape a single quote in SQL Server?](https://stackoverflow.com/questions/1586560/how-do-i-escape-a-single-quote-in-sql-server) — Richardissimo, Nov 20 '18 at 22:01

sacse · Answer 1 · 2018-11-21T06:57:56.623

1

Please use two quotes ('') instead of one to insert a single quote (') within the string.

Using Quoted_Identifier OFF:

edited Nov 21 '18 at 06:57

answered Nov 20 '18 at 23:58

sacse

3,634
2
15
24

I thank all of you for your help. Yes, double apostrophes. but, i have 2.5 million records, i cannot go one by one to make this change. as you can see, i have several examples of how the accents are in the input to my create/insert. – iqworks Nov 21 '18 at 00:23
are you importing the records from a file? – sacse Nov 21 '18 at 00:31
I thank all of you for your help. Yes, double apostrophes. but, i have 2.5 million records, i cannot go one by one to make this change. as you can see, i have several examples of how the accents are in the input to my create/insert. – iqworks Nov 21 '18 at 00:33
. for this accent ('Mas d\'Alins'' ), i was successfull with a search for "\" and replacing it with one space to look like this 'Mas d'Alins''. then , i was successful with replacing two '') with ') so it looked like this 'Mas d'Alins'. – iqworks Nov 21 '18 at 00:33
but, how about these three? --(143, 9, 2, 24.29861110, 53.20916670, 'Al Fuyay\''), --(156, 9, 2, 22.96666670, 54.30000000, 'Al Hama\'im'), --(739, 8, 2, 25.18222220, 56.22833330, 'Sa'if'),the one with 'if' gives a parameter error. – iqworks Nov 21 '18 at 00:33
hmmm, i will try a find and replace for all ' and replace them with '' – iqworks Nov 21 '18 at 00:37
NOPE, that find and replace of one apostrophe to two id terrible. Well, now i am waiting for a way to handle 2.5 million countries with accents. i looked at COLLATION, REGEX. – iqworks Nov 21 '18 at 00:45
try using SET QUOTED_IDENTIFIER OFF; you will need double quotes at the start and end for that... – sacse Nov 21 '18 at 01:00
BTW , FYI, when i did a scan and replace for the 'Mas d'Alins', it was accepted and inserted into my database table, no double quote needed. but i do understand the double quote thing. – iqworks Nov 21 '18 at 01:19
satishcse, I see it worked for you. i must be doing my collation wrong or something. will study up on SET_QUOTED_IDENTIFIER OFF. Thanks very much for this lead. – iqworks Nov 21 '18 at 16:46
satishcse, it works!! thank you so much. i had to enclose everything in double quotes and use QUOTED_IDENTIFIER OFF. – iqworks Nov 21 '18 at 17:14

SQL server - I am using an SQL file called world.SQL. It has an accented column holding data like 'Mas d\'Alins' ' - how do i insert this?

1 Answers1