vertical-blank
Issue when column name contains escaped enclosing character
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 ?
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 '\').
Please, what RDBMS are you using exactly (allowing `` for char literals and \ for escaping -- I know only \').
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.
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
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
;