5

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.

3
  • 2
    Possible duplicate of Using a string with single quotes in a table. No constants, you just need to escape the quotes. Nov 17, 2017 at 17:27
  • 2
    I think that's a different question. That's how you work around the feature not being there, but it still seems like a valid question to ask if functionality is present in a rdbms without assuming that every such question is a duplicate that explains the work-around. Nov 17, 2017 at 18:03
  • While the titles are indeed different, many SE questions are about more than what the asker says in question form. The second sentence indicates what they need to get done, so IMO "no there isn't an equivalent" is useless as opposed to providing the way forward. Not to say you can't do both, but since the more useful of the two already had a question.. Nov 17, 2017 at 20:07

2 Answers 2

3

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.

1

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!

2
  • 1
    double quotes aren't an issue for PostgreSQL either, dollar-sign quotes permit double quotes and single-quotes to be used -- well anything except $$, and if you need even better you can make the token more complex $DONGS$ Magic $$ '"' $$ $DONGS$. It's just a more complex form of literal creation. We mainly use them for quoting dynamic sql. There pretty awesome in that context where you have normal literal (') and identifier (") quoting. Nov 17, 2017 at 17:52
  • 1
    @EvanCarroll Dongalicious. Nov 17, 2017 at 17:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.