Repository: sql-formatter-org/sql-formatter
Pull request #342 (2022-07-23 15:55:48)

Add support for PLSQL Q strings
added support for the 2 cases of PLSQL Q strings:
- special delimiters: <, {, (, [ (must match with the respective >, }, ), ])
- common delimiters: any char that is not the above

surprisingly, the syntax allows for >, }, ), ] to be used as common delimiters
so both `Q'[foobar]'` and `q']foobar]'` are valid

see: https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements003.htm#i42617
for docs


Patch:
diff --git a/src/languages/plsql/plsql.formatter.ts b/src/languages/plsql/plsql.formatter.ts
index 312b5cda..ab3f747e 100644
--- a/src/languages/plsql/plsql.formatter.ts
+++ b/src/languages/plsql/plsql.formatter.ts
@@ -94,8 +94,10 @@ export default class PlSqlFormatter extends Formatter {
       reservedLogicalOperators: ['AND', 'OR', 'XOR'],
       reservedKeywords: keywords,
       reservedFunctionNames: functions,
-      // TODO: support custom-delimited strings: Q'{..}' q'<..>' etc
-      stringTypes: [{ quote: "''", prefixes: ['N'] }],
+      stringTypes: [
+        { quote: "''", prefixes: ['N'] },
+        { quote: "q''", prefixes: ['N'] },
+      ],
       identTypes: [`""`],
       identChars: { rest: '$#' },
       variableTypes: [{ regex: '&{1,2}[A-Za-z][A-Za-z0-9_$#]*' }],
diff --git a/src/lexer/regexFactory.ts b/src/lexer/regexFactory.ts
index d12a4661..953fb8a0 100644
--- a/src/lexer/regexFactory.ts
+++ b/src/lexer/regexFactory.ts
@@ -76,6 +76,32 @@ export const parameter = (paramTypes: string[], pattern: string): RegExp | undef
   return patternToRegex(`(?:${typesRegex})(?:${pattern})`);
 };
 
+const buildQStringPatterns = () => {
+  const specialDelimiterMap = {
+    '<': '>',
+    '[': ']',
+    '(': ')',
+    '{': '}',
+  };
+
+  // base pattern for special delimiters, left must correspond with right
+  const singlePattern = "{left}(?:(?!{right}').)*?{right}";
+
+  // replace {left} and {right} with delimiters, collect as array
+  const patternList = Object.entries(specialDelimiterMap).map(([left, right]) =>
+    singlePattern.replace(/{left}/g, escapeRegExp(left)).replace(/{right}/g, escapeRegExp(right))
+  );
+
+  const specialDelimiters = escapeRegExp(Object.keys(specialDelimiterMap).join(''));
+  // standard pattern for common delimiters, ignores special delimiters
+  const standardDelimiterPattern = String.raw`(?<tag>[^\s${specialDelimiters}])(?:(?!\k<tag>').)*?\k<tag>`;
+
+  // constructs final pattern by joining all cases
+  const qStringPattern = `[Qq]'(?:${standardDelimiterPattern}|${patternList.join('|')})'`;
+
+  return qStringPattern;
+};
+
 // This enables the following quote styles:
 // 1. backtick quoted using `` to escape
 // 2. square bracket quoted (SQL Server) using ]] to escape
@@ -94,6 +120,7 @@ export const quotePatterns = {
   "'''..'''": "'''[^\\\\]*?(?:\\\\.[^\\\\]*?)*?(?:'''|$)",
   '""".."""': '"""[^\\\\]*?(?:\\\\.[^\\\\]*?)*?(?:"""|$)',
   '{}': '(?:\\{[^\\}]*(?:$|\\}))',
+  "q''": buildQStringPatterns(),
 };
 
 const singleQuotePattern = (quoteTypes: QuoteType): string => {


Test patch:
diff --git a/test/plsql.test.ts b/test/plsql.test.ts
index 4303afe6..b85c7225 100644
--- a/test/plsql.test.ts
+++ b/test/plsql.test.ts
@@ -91,6 +91,17 @@ describe('PlSqlFormatter', () => {
     `);
   });
 
+  it('supports Q custom delimiter strings', () => {
+    expect(format("q'<test string < > 'foo' bar >'")).toBe("q'<test string < > 'foo' bar >'");
+    expect(format("NQ'[test string [ ] 'foo' bar ]'")).toBe("NQ'[test string [ ] 'foo' bar ]'");
+    expect(format("nq'(test string ( ) 'foo' bar )'")).toBe("nq'(test string ( ) 'foo' bar )'");
+    expect(format("nQ'{test string { } 'foo' bar }'")).toBe("nQ'{test string { } 'foo' bar }'");
+    expect(format("Nq'%test string % % 'foo' bar %'")).toBe("Nq'%test string % % 'foo' bar %'");
+    expect(format("Q'Xtest string X X 'foo' bar X'")).toBe("Q'Xtest string X X 'foo' bar X'");
+    expect(format("q'$test string $'$''")).toBe("q'$test string $' $ ''");
+    expect(format("Q'Stest string S'S''")).toBe("Q'Stest string S' S ''");
+  });
+
   it('formats INSERT without INTO', () => {
     const result = format(
       "INSERT Customers (ID, MoneyBalance, Address, City) VALUES (12,-123.4, 'Skagen 2111','Stv');"