Issue when column name contains escaped enclosing character

[fabiencelier]

The query

SqlFormatter.of(Dialect.PlSql)
        .extend(cfg -> cfg.plusOperators("->"))
        .format("SELECT `My field with \\` back () <<<<<< tick and a verryyyyyyyy long name` FROM MY_TABLE");

produces

SELECT
  `My field with \` back < < < < < < tick
  and a verryyyyyyyy long name ` FROM MY_TABLE

Which is no longer a valid query as the name has been split over multiple lines and whitespaces have been included. I think that the first ` is detected as the end of the name even though it is escaped with a \.

Is there any way to specify such an escape pattern in column names ?

[manticore-projects]

Greetings!

Is there any way to specify such an escape pattern in column names ?

It really depends on your RDBMS and SQL flavor. Standard character quote is single simple quote ' and standard escape of this quoting character would be '':

SELECT 'My field with '' back () <<<<<< tick and a verryyyyyyyy long name' FROM MY_TABLE

If you want to use backslash for quoting, you would need to set it explicitly as it collides with the LIKE ... ESCAPE ... clause (e. g. LIKE 'foo\_bar' ESCAPE '\').

[manticore-projects]

Please, what RDBMS are you using exactly (allowing `` for char literals and \ for escaping -- I know only \').

[manticore-projects]

Here is an illustration of the issue, you will need to set the option BackSlashQuoting in the UI to make it work.

The challenge is to define a Regular Expression matching anything between ' and ', which does not trigger on \' and still works for LIKE ... ESCAPE '\' (which actually ends exactly with \'. Thus it needs to be a kind of a switch depending on the dialect and RDBMS.

[fabiencelier]

Hello, In ClickHouse for instance we can use ` to escape the identifiers: https://clickhouse.com/docs/en/sql-reference/syntax#identifiers

If the name of the column contains a ` then it must be escape with \

Is there a way to provide our own Regular Expression for that ? As far as I understand these are hardcoded in an enum and cannot be extended

[manticore-projects]

Greetings.

ClickHouse documentation just proves my point: ' and \' is allowed and JSQLFormatter supports it -- although you will need to activate the Option backSlashQuoting.

It did not see anything about `` and \`` though. Please note: '' is not `` and your example shows a String Literal but not an Identifier.

For Identifiers, 3 variants would work:

SELECT `My field with `` back () <<<<<< tick and a verryyyyyyyy long name`
FROM my_table
;

-- works, but won't escape the ` and instead will print \`
SELECT "My field with \` back () <<<<<< tick and a verryyyyyyyy long name"
FROM my_table
;

SELECT "My field with "" back () <<<<<< tick and a verryyyyyyyy long name"
FROM my_table
;