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

[Rao2821]

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.

[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 · Accepted solution]

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 · Accepted solution]

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 · Accepted solution]

@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