Important: Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

How to format SQL strings?



  • 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]


  • Moderators

    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)



  • Of course I already know that;

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



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

    1. 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
    @

    1. 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.



  • [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.



  • [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.


  • Moderators

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

    Good luck with your search.



  • 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?


Log in to reply