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

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, '''', '''''')

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

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.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related