Using Regex to determine what kind of SQL statement a row is from a list?

Question

I have a large list of SQL commands such as

SELECT * FROM TEST_TABLE
INSERT .....
UPDATE .....
SELECT * FROM ....

etc. My goal is to parse this list into a set of results so that I can easily determine a good count of how many of these statements are SELECT statements, how many are UPDATES, etc. so I would be looking at a result set such as

SELECT 2
INSERT 1
UPDATE 1
...

I figured I could do this with Regex, but I'm a bit lost other than simply looking at everything string and comparing against 'SELECT' as a prefix, but this can run into multiple issues. Is there any other way to format this using REGEX?

You can do `select count(case when s like 'SELECT%' then 1 end) as selects, count(case when s like 'UPDATE%' then 1 end) as updates, count(case when s like 'INSERT%' then 1 end) as insert from t`. — The Impaler, Sep 20 '22 at 18:36
To be clear, do you need an SQL solution? Or is this list just in a text file and you could use text-processing tools? — Bill Karwin, Sep 20 '22 at 23:25

score 1 · Accepted Answer · answered Sep 20 '22 at 22:04

You can add the SQL statements to a table and run them through a SQL query. If the SQL text is in a column called SQL_TEXT, you can get the SQL command type using this:

upper(regexp_substr(trim(regexp_replace(SQL_TEXT, '\\s', ' ')),
      '^([\\w\\-]+)')) as COMMAND_TYPE

score 0 · Answer 2 · answered Sep 20 '22 at 23:22

You'll need to do some clean up to create a column that indicates the type of statement you have. The rest is just basic aggregation

with cte as
(select *, trim(lower(split_part(regexp_replace(col, '\\s', ' '),' ',1))) as statement
 from t)

select statement, count(*) as freq
from cte
group by statement;

score 0 · Answer 3 · answered Sep 22 '22 at 10:36

SQL is a language and needs a parser to turn it from text into a structure. Regular expressions can only do part of the work (such as lexing).

Regular Expression Vs. String Parsing

You will have to limit your ambition if you want to restrict yourself to using regular expressions.

Still you can get some distance if you so want. A quick search found this random example of tokenizing MySQL SQL statements using regex https://swanhart.livejournal.com/130191.html

Using Regex to determine what kind of SQL statement a row is from a list?

3 Answers3