How to replace character ' with character '' in SQL Server

Question

I want a select statement like this

select concat ('insert into Place (Id, Name) values (' Id, ',', '''', PlaceName , '''', ')' ) 
from Address

The values are:

Id   PlaceName
------------------
 1   Amsterdam
 2   's-Gravenhage
 3   Rotterdam
 4   Utrecht
 5   's-Hertogenbosch
 6   Groningen

How can you create an output like this?

insert into Place (Id, Name) values (1, 'Amsterdam')
insert into Place (Id, Name) values (2, '''s-Gravenhage')
insert into Place (Id, Name) values (3, 'Rotterdam')
insert into Place (Id, Name) values (4, 'Utrecht')
insert into Place (Id, Name) values (5, '''s-Hertogenbosch')
insert into Place (Id, Name) values (6, 'Groningen')

I tried to use Replace(PlaceName, ''', '''') or something...

Why do this at all? The problem is caused by the use of dynamic sql, not the quotes — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Oct 27, 2020 at 16:13
Again, why do this at all? Why use dynamic sql? You won't have any issues if you use parameterized queries. Or if you export the data with eg bcp or any other tool, then import them to another database — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Oct 27, 2020 at 16:14
I want a simple solution for creating insert-statements. It's just for once. and I want to replace a single ' into a double '. — user1531040
– user1531040, Commented Oct 27, 2020 at 16:17
But that doesn't answer the why @PanagiotisKanavos is asking here, user1531040. I smell an XY Problem here. — Thom A
– Thom A ♦, Commented Oct 27, 2020 at 16:23

iamdave · Accepted Answer · 2020-10-27 16:17:41Z

2

You were very close in your initial attempt, you just need to escape the single quotes by doubling them up, as is actually being done in your first sql statement:

replace(PlaceName, '''', '''''')

answered Oct 27, 2020 at 16:17

iamdave

12.3k3 gold badges29 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Lukasz Szozda · Accepted Answer · 2020-10-27 16:21:51Z

1

Using replace:

select FORMATMESSAGE('INSERT INTO Place(id, Name) VALUES (%i, ''%s'');'
                     ,Id ,REPLACE(PlaceName, '''', ''''''))
from Address;

db<>fiddle demo

Warning: There are other, better ways of scripting data. Generating INSERT INTO is supported by SSMS or Azure Data Studio.

answered Oct 27, 2020 at 16:21

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Collectives™ on Stack Overflow

How to replace character ' with character '' in SQL Server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related