Is there a way to escape single quotes on the fly, without having to escape every occurrence of a single quote with two single quotes?

I'm looking for a T-SQL equivalent of PL/SQL's quote literal q'[this is PL SQL's quoted literal]'

CodePudding user response：

When you read from xml, you should be able to cast to VARCHAR datatype and INSERT, UPDATE accordingly.

Sample code below:

DECLARE @table table(quotedstring varchar(100))
DECLARE @xml xml = 
'<root><t>this is PL SQL&apos;s quoted literal</t><t>this is second PL SQL&apos;s quoted literal</t></root>'

insert into @table
select t.value('t[1]','varchar(100)') as qstring from @xml.nodes('root') as x(t) 

UPDATE @table
SET quotedstring = (SELECT t.value('t[2]','varchar(100)') from @xml.nodes('root') as x(t))

SELECT * from @table

CodePudding user response：

In T-SQL you can have two single coats together to exclude single coats (no double coat).

for example:

'this is PL SQL's quoted literal'

Should change as follows:

'this is PL SQL''s quoted literal'