Sql-server – Equivalent to PostgreSQL’s Dollar-quoted String Constants for SQL Server

postgresqlsql serverstring

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.

Best Answer

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.

Related Solutions

Postgresql – Postgres/pgloader: setting a delimiter for multiple line text records

I don't believe you can use pgloader in this way if any of your HTML contains character sequences that would be interpreted in the context of CSV. For example, any backslash followed by digits is perfectly valid (and not-special) HTML, but would be transformed to something else by pgloader while importing. I can't even estimate how pgloader will handle the double-quote and single-quote symbols scattered around in your documents.

You would also need a byte that doesn't exist anywhere in your content to serve as a row delimiter; you could use some low-value control byte for this purpose assuming your HTML is valid and doesn't contain any of these special bytes. This naturally depends on your chosen character encoding, and is rendered worse if you have multiple encodings among your documents.

Assuming everything is UTF-8, the NUL byte should not appear in any UTF-8 stream. Assuming you aren't concerned about special CSV character sequences, you could attempt to do this import using NUL as your separator value:

--with "lines terminated by '\0x00'"

I wouldn't recommend this avenue though for fear of subtle corruption of your documents (as noted above).

SQL Server – Using Column Names in Separate Table

What you're looking for is Dynamic SQL Pivots. A PIVOT will turn row values into columns based on an aggregate, however you need to define the column names as part of the PIVOT, luckily we can do that on the fly with dynamic SQL. The following should generate the result set you want for a particular company (replace rowValues with your table name):

DECLARE @SQL NVARCHAR(MAX)
DECLARE @Columns NVARCHAR(MAX)
DECLARE @Company NVARCHAR(5) = '01'

SET @Columns = STUFF( (SELECT ',['+H.description+']' AS [data()] 
                        FROM dbo.Headers H
                        WHERE H.company = @Company  
                        ORDER BY H.fieldNumber
                        FOR XML PATH('')),1,1,'')

SET @SQL = '
SELECT company,jobNumber,'+@Columns+'
FROM
(
    SELECT h.company,RV.jobNumber,RV.information,h.description
    FROM Headers h
            INNER JOIN dbo.RowValues RV
                ON RV.fieldNumber = h.fieldNumber
                   AND RV.company = h.company
    WHERE h.company = '+@Company+'  
) as Data
PIVOT
(
    MAX(information) FOR [description] IN ('+@Columns+')
) as p
ORDER BY jobNumber ASC'

EXEC sp_executesql @sql;

Unfortunately due to the possibility that different companies will have different columns there is no reliable way to combine all the the possible sets the above query could generate. Depending on how you want to use this query the easy option is to loop through all the companies and call an SP that contains the above, allowing you to output each company as a separate SELECT. Or you can do something with your SSIS package to output each one into a file.

If you want to insert the data into a table matching the calculated schema you can do a SELECT INSERT and a bit more dynamic SQL to get the data where it needs to go.

Best Answer

Related Solutions

Postgresql – Postgres/pgloader: setting a delimiter for multiple line text records

SQL Server – Using Column Names in Separate Table

Related Question