How to extract the table name and column name from SQL commands in excel sheet

I have a table where the first column contains SQL commands such as DELETE and UPDATE statements. I would like to read each row, identify the table name and column name from that row, and print them in separate columns titled "Table Name" and "Column Name" respectively.

Commands.xlsx

Macros

Accepted answers

caltang

Adding to what @Kenda has recommended, you can use a filter tool to split out the UPDATE and DELETE statements because it will be easier to REGEX for each individually and then Union the result again.

You can use a Parse tool to get what you desire from the text.

caltang

Sorry, I meant use a REGEX tool to parse out like so:

for DELETE:

For the table name: "from\s+(\w+)"

For the column names: "(?<=set\s|where\s)\w+"

for UPDATE:

For the table name: "update\s+(\w+)"

For the column names: "(?<=set\s)\w+\s*=|\w+(?=\s*where)"

Hope this helps give some ideas…

Kenda

@Rao2821

Here is an expression that you can use in a Formula tool to get the table name in the first two delete statements:

REGEX_Replace([Commands], ".*from (.*?) where.*", "$1")

The expression looks for the characters between the words "from " and the first instance of " where" and outputs what is between them (identified using parenthesis in this expression).

Try to build your own expressions to get the other pieces you're looking for!

https://regex101.com/ is a great resource to help learn RegEx.

All comments

caltang

Could you provide some samples or images to better help us understand your requirement? Not too sure what you mean....

Kenda

Hi @Rao2821

You may be able to use a Text to Columns tool to separate out your statements and get the pieces you're looking for. Alternatively, RegEx may be a possibility. Could you provide a couple lines of sample data?

Rao2821

Hey @Kenda,

I have attached the sample data. Thank you

Rao2821

Hi @caltang

I have attached the sample data. Thank you.

caltang

You can use a Parse tool to get what you desire from the text.

caltang

Sorry, I meant use a REGEX tool to parse out like so:

for DELETE:

For the table name: "from\s+(\w+)"

For the column names: "(?<=set\s|where\s)\w+"

for UPDATE:

For the table name: "update\s+(\w+)"

For the column names: "(?<=set\s)\w+\s*=|\w+(?=\s*where)"

Hope this helps give some ideas…

Kenda

@Rao2821

Here is an expression that you can use in a Formula tool to get the table name in the first two delete statements:

REGEX_Replace([Commands], ".*from (.*?) where.*", "$1")

The expression looks for the characters between the words "from " and the first instance of " where" and outputs what is between them (identified using parenthesis in this expression).

Try to build your own expressions to get the other pieces you're looking for!

https://regex101.com/ is a great resource to help learn RegEx.

ArnaldoSandoval

Hi @Rao2821

The attached workflow may do the job, take a look at it.

Arnaldo

Parse_Sql_Command.yxmd

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335