Equivalent to PostgreSQL's Dollar-quoted String Constants for SQL Server

Question

Is there an equivalent of PostgreSQL's Dollar-quoted String Constants, on SQL Server?

I would like to enter HTML string literals that would potentially contain single or double quotes in them.

Example:

UPDATE table_name
SET    column_name = $$
  Here's a string that contains "double quotes".
$$
WHERE  condition = true

Edit: The reason I'm asking this is because I would like to update some rather big HTML "web parts" that are stored in the database (yuck!). Because at work we use that kind of CMS (which I won't give you the name). So I don't want to have to escape my single quotes every time. That's why I'm asking about that kind of feature. If I understand correctly, QUOTENAME would still require me to double my single quotes.

Answer 1

This is a PostgreSQL extension of the spec. There is nothing like it in SQL Server. The idea is that you can quote everything that doesn't have the $$[token]$$. If the optional token is absent it looks like $$. Thus the name, Dollar-quoted String Constants

Dollar quoting is not part of the SQL standard, but it is often a more convenient way to write complicated string literals than the standard-compliant single quote syntax. It is particularly useful when representing string constants inside other constants, as is often needed in procedural function definitions. With single-quote syntax, each backslash in the above example would have to be written as four backslashes, which would be reduced to two backslashes in parsing the original string constant, and then to one when the inner string constant is re-parsed during function execution.

So you can pass something like this,

$$ SELECT foo FROM "mySchema"."myTable" WHERE bar='foo''bar'; $$

or, even this.

$OUTER$ SELECT foo FROM "mySchema"."myTable" WHERE bar=$$foo'bar$$; $OUTER$

And we do that frequently with modules like tablefunc

As a workaround, in SQL Server, you just have to be escape the single quotes and double escape the single-quotes in the single quotes. This is the standard method of literal-quoting.

' SELECT foo FROM "mySchema"."myTable" WHERE bar=''foo''''bar''; '

The '''' is what we're trying to avoid here. That's fugly.

Answer 2

Double quotes aren't an issue in SQL Server.

For example:

CREATE TABLE dbo.Dummy
(
    Id INT NOT NULL,
    NameThing VARCHAR(100)
) ;
GO

INSERT dbo.Dummy ( Id, NameThing )
VALUES ( 1, NULL )

UPDATE d
SET d.NameThing = ' "Your String" '
FROM dbo.Dummy AS d

Works just fine. You only have to be careful with single quotes.

UPDATE d
SET d.NameThing = ' "Your String Thong''s Stinky" '
FROM dbo.Dummy AS d

Here we have to add an extra single quote to escape it out and preserve the integrity of the string. Or something else that sounds fancy.

The rules are the same for variable assignment as well.

DECLARE @YourString VARCHAR(100) = ' "Your String" '
SELECT @YourString
GO 

DECLARE @YourString VARCHAR(100) = ' "Your String Thong''s Stinky" '
SELECT @YourString
GO 

I'm not a big Postgres guy (for you), so if there's some ancient neck-bearded magic in the $$double dollar sign$$ identifier that I'm missing, lemme know.

As an extra bit, SQL Server also has the QUOTENAME() function, but I'm not sure this is equivalent behavior, or even helpful to you.

Hope this helps!

Answer 3

I do not see an answer with QUOTED_IDENTIFIER:

SET QUOTED_IDENTIFIER OFF;
DECLARE @YourString VARCHAR(100) = "_ 'Your String' _"
SELECT @YourString
SET QUOTED_IDENTIFIER ON;

You use double quotes on string itself and you don't need to escape every single quote. Works well on dynamic queries (they have a lot of single quotes that you don't need to escape now).

This is not the same as

$q$postgres ' without escape$q$

q'[oracle ' without escape]'