Sublime Forum

Formatting MySQL queries

#1

I work a lot with exported views from MySQL databases, and the following would be a great time saver for me:

The query is exported as one long line of text, and I would like to insert a line break after each occurence of

AS `something`,

( That is the word AS (always in capitals) + column name (always some text without spaces) + comma)

I understand how to search for a single word in ST3, then select all matches and then press return - but I do not understand how to search for :

AS `something`,

Hope this makes sense, thanks for all help!

0 Likes
#2

That there is a job tailor made for a Regular Expression. If you’re not familiar with them, it’s basically a special language for matching text using rules. The full scope of that is worthy of some research, such as here.

For your case, you would open up the replace dialog with Find > Replace... from the menu (or the associated keyboard shortcut, which the menu will tell you if you don’t know it).

In the options button on the left, make sure that regular expressions are turned on, and then enter the following:

Find What: (AS `.*`,)
Replace With: \1\n

Here the magic is in three parts:

  1. The construct .* means “match any sequence of any character”
  2. The parenthesis wrapping the search text mean “capture whatever matches inside for later use”
  3. The \1 in the replacement means "Insert the text captured by the first set of parenthesis
  4. The \n means “insert a newline”

Well, clearly I can’t count, but the result is that the search term matches regardless of what’s inside of the back ticks and the replacement uses what was matched to put it back along with the newline that you want.

When you’re working with text a lot, regular expressions are a handy tool to have in your back pocket.

0 Likes
#3

Thanks a lot, OdatNurd for taking the time to write up such a meaningful and informative answer.

I still can not get it work exactly as intended, though. I get only “1 match” where thee should have been many.

Here is a screen shot. Perhaps I described the problem a bit poorly. There should have been many matches in this view, but ST seems to find only one…

Screenshot

Again, thanks for all help!

Egil.

0 Likes
#4

Whoops, my bad on that one; you described it correctly but in the example I pulled for myself there was only a single as portion in the query, so I didn’t notice.

The reason that didn’t perform as expected is because the * operation (which means “0 or more of the
preceding”) is what we call “greedy”; it tries to match as much as it possibly can. So it matches everything from the start of the first AS to the end of the last one.

Something like the following Regex is probably a better match (pun sorta intentional) for what you’re trying to do; the replacement text is unchanged:

Find What:  (AS `[^`]*`,?)

The differences here from above are:

  1. [^`] means “match anything that is not a ` character”, which constrains the match so that it can’t greedily eat all of the text.
  2. ,? (specifically the addition of the question mark here) means “match 0 or 1 comma”. The reason for that is to catch situations where the last AS clause isn’t followed by comma:
SELECT field AS `something`, field2 AS `somethingElse` FROM TableName;
0 Likes
#5

That solved it exactly as I had hoped. Thanks a lot for helping out!

Will save me lots of time, which I should instead spend on trying understand what that regex does exactly :slightly_smiling:

Thanks again for your time. I’m new with Sublime Text, happily purchased it after testing it a couple of hours.

Egil.

1 Like
#6

you could also consider using a package designed to do the hard work for you:
https://packagecontrol.io/search/format%20sql

1 Like