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

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?

CodePudding user response：

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

CodePudding user response：

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;