SQL Select Multiple Part of string!

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 t开发者_JAVA百科he 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!

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.