CODEWITHSUNDEEP
javasqlsql-server
loknath
loknath

Reputation: 758

Replacing escape characters around table-names, column-names in an SQL query

For example

query = " select "2017-06-08" as new_colum, "true" as my_flag, "column1", "column2" from "table1" "

The above query should be changed as:

select "2017-06-08" as new_column, "true" as my_flag, [column1], [column2] from [table1]. (MS SQL format)

Can I use some parser like jsql parser? Or Is there any other better way?

Upvotes: 3

Views: 1689

Answers (2)

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

SQL Server Feature Pack comes with a dedicated API for parsing T-SQL source code: [ TransactSql ScriptDom available within Microsoft.SqlServer.TransactSql.ScriptDom namespace.

Among different classes, there is a TSqlParser class with subclasses for every SQL Server version. For example, for SQL Server 2008 there is following class TSql100Parser.

Regarding this question, here I have found an C# example about parsing a SELECT statement.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521073

If it were not for that your dates are surrounded by double quotes, we could have just done a blanket replacement of "(.*?)" with [$1] using String#replaceAll(). But the presence of double quoted dates makes the problem more difficult. My updated answer uses the following pattern to target only non dates in double quotes:

(\s+)"([^\d].*?)"

This will only match a quoted term preceded by at least one whitespace character, and whose first character inside the quotes is not a digit. This should rule out all dates, and it should not rule out any column since SQL Server column names cannot begin with a number.

I make the assumption here that every quoted column is preceded by whitespace. This should be OK, assuming the first word in your query string is always a keyword like SELECT or UPDATE.

String query = "select \"2017-06-08\" as new_colum, \"column1\", \"column2\" from \"table1\"";
query = query.replaceAll("(\\s+)\"([^\\d].*?)\"", "$1[$2]");
System.out.println(query);

Output:

select "2017-06-08" as new_colum, [column1], [column2] from [table1]

By the way, if you're wondering what the significance is of checking for whitespace before the beginning of a quoted term, try taking out that requirement from the regex. You will see that replaceAll() will than falsely pick up on a closing quote as being the start of a term, which it is not.

Demo

Upvotes: 7

Related Questions