How to highlight SQL keywords using a regular expression?

Question

I would like to highlight SQL keywords that occur within a string in a syntax highlighter. Here are the rules I would like to have:

• Match the keywords SELECT and FROM (others will be added, but we'll start here). Must be all-caps
• Must be contained in a string -- either starting with ' or "
• The first word in that string (ignoring whitespace preceding it) should be one of the keywords.

This of course is not comprehensive (can ignore escapes within a string), but I'd like to start here.

Here are a few examples:

• SELECT * FROM main -- will not match (not in a string)
• "SELECT name FROM main" -- will match
• "
  SELECT name FROM main" -- will match
• """Here is a SQL statement:

SELECT * FROM main""" -- no, string does not start with a keyword (SELECT...).

The only way I thought to do it in a single regex would be with a negative lookbehind...but then it would not be fixed width, as we don't know when the string starts. Something like:

• (?<=["']\s*(SELECT)\s*)(SELECT|FROM)

But this of course won't work:

Would something like this be possible to do in a single regex?

Answer 1

A suitable regular expression is likely to get pretty complex, especially as the rules evolve further. As others have noted, it may be worth considering using a parser instead. That said, here is one possible regex attempting to cover the rules mentioned so far:

(["'])\s*(SELECT)(?:\s+.*)?\s+(FROM)(?:\s+.*)?\1(?:[^\w]|$)

Online Demos

1. Debuggex Demo
2. Regex101 Demo

Explanation

As can be seen in the above visualisation, the regex looks for either a double or single quote at the start (saved in capturing group #1) and then matches this reference at the end via \1. The SELECT and FROM keywords are captured in capturing groups #2 and #3. (The (?:x|y) syntax ensures there aren't more groups for other choices as ?: at the start of a choice excludes it as a capturing group.) There are some further optional details such as limiting what is allowed between the SELECT and FROM and not counting the final quotation mark if it is immediately succeeded by a word character.

Results

SELECT * FROM tbl        -- no match - not in a string
"SELECT * FROM tbl"      -- matches - in a double-quoted string
'SELECT * FROM tbl;'     -- matches - in a single-quoted string
'SELECT * FROM it's      -- no match - letter after end quote
"SELECT * FROM tbl'      -- no match - quotation marks don't match
'SELECT * FROM tbl"      -- no match - quotation marks don't match
"select * from tbl"      -- no match - keywords not upper case
'Select * From tbl'      -- no match - still not all upper case
"SELECT col1 FROM"       -- matches - even though no table name
'  SELECT  col1  FROM '  -- matches - as above with more whitespace
'SELECT col1, col2 FROM' -- matches - with multiple columns

Possible Improvement?

It might also be necessary to exclude quotation marks from the "any character" parts. This can be done at the expense of increased complexity using the technique described here by replacing both instances of .* with (?:(?!\1).)*:

(["'])\s*(SELECT)(?:\s+(?:(?!\1).)*)?\s+(FROM)(?:\s+(?:(?!\1).)*)?\1(?:[^\w]|$)

See this Regex101 Demo.

Answer 2

You could use capturing groups:

(.*["']\s*\K)(?(1)(SELECT|FROM).*(SELECT|FROM)|)

In this case $2 would refer to the first keyword and $3 would refer to the second keyword. This also only works if there are only two keywords and only one string on a line, which seems to be true in all of your examples, but if those restrictions don't work for you, let me know.

Answer 3

Just tested the regexp bellow:

If you need to add other commands the thing may get a little trick, because some keywords doesn't apply. Eg: ALTER TABLE mytable or UPDATE SET col = val;. For these scenarios you will need to create subgroups and the regexp may become slow.

Best regards!

Answer 4

If I understand your requirements well I suggest that:

/^'\s*(SELECT)[^']*(FROM)[^']*'|^"\s*(SELECT)[^"]*(FROM)[^"]*"/m

[Regex Fiddle Demo]

Explanation:

• When you need to check start of a string; use ^.
• When you need to accept 0-n spaces; use \s*.
• When you need to accept new-line or multi-line strings; use m flag over your regex.
• When you need to use Case-Sensitive mode; Don't use i flag over your regex.
• When you need to block a string between a specific character like "; use [^"]* instead of .* that will protects first end of block.
• When you need to have a block with similar start and end characters like ' & "; use ' '|" " instead of ['"] ['"].

---

Update:
If you need to capture any special keyword after verifying existence of SELECT keyword after start of your string, I can update my solution to this:

/^'\s*(SELECT)([^']*(SELECT|FROM))+|^"\s*(SELECT)([^"]*(SELECT|FROM))+/m

Answer 5

without parsing of quoted strings
could be done using \G and \K construct

(?:"\s*(?=(?:SELECT|FROM))|(?<!^)\G)[^"]*?\K(SELECT|FROM)

demo