In this one stored procedure being called by an API to update a record, there's lots of instances of this:

SET @NewField = REPLACE(ISNULL(@InputField, ''), '''', '''''')

I get the ISNULL part, and I know '' escapes a single quote, but I cannot figure out what the intent of four and six quotes is?

CodePudding user response：

A literal quote in a string literal needs to be escaped by doubling it. So the (interpreted, runtime) value of '''' is just one single quote. Replacing what looks like four quotes by what looks like six quotes is t-sql syntax to mean:

Search the string for occurrences of a single quote, and replace each with two consecutive single quotes.

Thus SQL's syntax is weird sometimes would become SQL''s syntax is weird sometimes.

One application of this I've seen is to prepare a string for subsequent literal execution (with the "execute" command). This is a sensitive area of SQL programming because it deals with security: Correctly escaping single quotes is one building block of safe-for-scripting queries.