SQL Select Multiple Part of string!

Question

I need help. I want to extract multiple parts of sql statement string. ex. I have the example of string:

UPDATE table SET **XXX** = '1', **YYY** = 2 WHERE ID = 24125;

So, I want to extract these values in bold ('xxx' and 'yyy'. In general, names of columns changed via Update statement.

Here is the example using the substring function, for selecting only one part, but in my case I need multiple parts:

statement like '%UPDATE%' then SUBSTRING(statement,NULLIF(CHARINDEX('SET',statement),0)+LEN('SET')+1, NULLIF(CHARINDEX('=',statement),0) -(NULLIF(CHARINDEX('SET',statement),0)+LEN('SET')+1))

Thank you!

At least I need to extract the column names, whether in the UPDATE statement there is one or more. I tried with T-SQL and substring function, but I can not take multiple parts. Here is my example for one part: statement like '%UPDATE%' then SUBSTRING(statement,NULLIF(CHARINDEX('SET',statement),0)+LEN('SET')+1, NULLIF(CHARINDEX('=',statement),0) -(NULLIF(CHARINDEX('SET',statement),0)+LEN('SET')+1)) — asterix55
– asterix55, Commented May 2, 2011 at 21:26
It won't be easy to do this in an at all robust way in TSQL. Why do you have the requirement? (There might be an alternative way to do whatever it is you are trying to do). And where does this statement come from in the first place? — Martin Smith
– Martin Smith, Commented May 2, 2011 at 21:41
it's usual UPDATE queries done by users. Therefore I need to take the columns that have been updated. In this case, I will need to extract the values between "SET" and "=", and between "," and "=". — asterix55
– asterix55, Commented May 2, 2011 at 21:57
@asterix55 - You can access COLUMNS_UPDATED() inside an UPDATE trigger. But where are you getting the UPDATE statements themselves from? Are these historic queries? If so using C# or similar will likely be much more productive than trying to do it in TSQL. For example see stackoverflow.com/questions/5792507/… — Martin Smith
– Martin Smith, Commented May 2, 2011 at 22:01
I have to use T-SQL, since I am saving these values in specific format and then in .txt file. — asterix55
– asterix55, Commented May 2, 2011 at 22:10

Jonathan Leffler · Accepted Answer · 2011-05-03 00:45:06Z

To do the job reliably, you are going to have to write a decent portion of the parser for SQL (or TSQL). And that's a non-trivial exercise!

You have not taken into account all the legal variations of UPDATE statements. For example, you might get:

UPDATE Sometable
   SET (Col1, Col2, Col3) = ((SELECT Value1, Value2, Value3 FROM ... WHERE ...)),
       Col4 = (SELECT Value4 FROM ... WHERE ...)
 WHERE ...;

And that's before you take into account operations like join updates.

Any simple-minded solution that doesn't handle such queries has the potential to run foul of power users, or hackers who realize what you do parse and want to bypass your detection code. Don't forget that I could put a comment - or even several comments - between any of the tokens in the UPDATE statement. These might or might not make it into the audit log - but then there are probably hints which look like comments, and so on.

in this case, do you have any idea how to select the column names that have been changed?

Collectives™ on Stack Overflow

SQL Select Multiple Part of string!

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related