How to format SQL strings?

mbnoimi

Hello,

Does any one knows a tool, utility or Qt Creator plugin for formatting C++ friendly strings?

This issue nagging me very much because whenever I copy SQL string I've to format the content manually before using it inside my C++\Qt source code... Example:

I've to format:
[code]"SELECT public."Accounts".name, public."Analyze".name, year_2014."Results".value, year_2014."Results"."date", year_2014."Results".requestnumber FROM year_2014."Results" INNER JOIN public."Accounts" ON (year_2014."Results".account_id = public."Accounts".id) INNER JOIN public."Analyze" ON (year_2014."Results".analyze_id = public."Analyze".id) WHERE public."Analyze".isparent = 'FALSE' AND public."Accounts".id = %1 OR public."Accounts".name LIKE '%%2%' UNION SELECT public."Accounts".name, public."SubAnalyze".name, year_2014."Results".value, year_2014."Results"."date", year_2014."Results".requestnumber FROM year_2014."Results" INNER JOIN public."Accounts" ON (year_2014."Results".account_id = public."Accounts".id) INNER JOIN public."SubAnalyze" ON (year_2014."Results".subanalyze_id = public."SubAnalyze".id) WHERE public."Accounts".id = %1 OR public."Accounts".name LIKE '%%2%'";[/code]

To be like this:
[code]"SELECT public."Accounts".name, public."Analyze".name, year_2014."Results".value, year_2014."Results"."date", year_2014."Results".requestnumber FROM year_2014."Results" INNER JOIN public."Accounts" ON (year_2014."Results".account_id = public."Accounts".id) INNER JOIN public."Analyze" ON (year_2014."Results".analyze_id = public."Analyze".id) WHERE public."Analyze".isparent = 'FALSE' AND public."Accounts".id = %1 OR public."Accounts".name LIKE '%%2%' UNION SELECT public."Accounts".name, public."SubAnalyze".name, year_2014."Results".value, year_2014."Results"."date", year_2014."Results".requestnumber FROM year_2014."Results" INNER JOIN public."Accounts" ON (year_2014."Results".account_id = public."Accounts".id) INNER JOIN public."SubAnalyze" ON (year_2014."Results".subanalyze_id = public."SubAnalyze".id) WHERE public."Accounts".id = %1 OR public."Accounts".name LIKE '%%2%'";[/code]

JKSH

Hi,

You could do a find+replace before pasting the string into your .cpp file. (e.g. in Microsoft Notepad, the shortcut is Ctrl+H)

mbnoimi

Of course I already know that;

My question is very specified. I need a tool for reformat this kind of strings.

andreyc

There is no such specific tool but you have two options:

QtCreator supports some subset of Vim and its regular expressions.
You can switch to Vim mode by Alt+V,Alt+V
Put cursor on the string and enter to command mode by :

For this particular string you can use the following RegEx and press enter.
@
s/."([^"]*)"/.\"\1\"/g
@

QtCreator "Search & Replace" also supports "RegEx":http://qt-project.org/doc/qtcreator-3.1/creator-editor-finding.html
It is based on "QRegExp":http://qt-project.org/doc/qt-5/qregexp.html#details

An expression to search
@
."([^"]*)"
@

An expression to replace
@
."\1"
@

[EDIT]; fixed a replace expression.

mbnoimi

[quote author="andreyc" date="1405395356"]There is no such specific tool
[/quote]

No there is a such tool; I used it before but it's a commercial one (Navicat: it has "Copy to C++" action which format the string for me); Beside that I remember that some contributor started a Qt Creator plugin adds "Special Paste" action for formatting such kind of strings but I missed his post (I read it in Qt's bug tracker).

I'm sure there is such kind of tools (commercial and open source too) but I forgot it the correct term for find them in google.

andreyc

[quote]No there is a such tool; I used it before [/quote]
Right. I guess I wanted to say that I don't know such tool.

JKSH

I don't know of any such tool either, sorry.

Good luck with your search.

mikeosoft

I might be wrong with this, I wouldn't consider myself an SQL expert, but this seems to works for me using SQLite within Qt.

In my SQL I use the single quote ' rather than the double quote " and it doesn't need escaping, so I avoid the problem.

I would also have thought that table and field names only need quotation marks if they have spaces in them. I use underscores in field names to avoid that and only use the single quote when I am including strings for things like date formats within the strftime function.

I guess things could be different with different SQL libraries within Qt?