Parsing Transact SQL with RegEx

Question

I'm quite inexperienced with RegEx - just an occasional straighforward RegEx for a programming task that I worked out by trial and error, but now I have a serious regEx challenge:

I have about 970 text files containing Sybase Transact SQL snippets, and I need to find every table name in those files and preface the table name with ' #'. So my options are to either spend a week editing the files by hand or write a script or application using regEx (Python 3 or Delphi-PRCE) that will perform this task.

The rules are as follows:

Table names are ALWAYS upperCase - so I'm only looking for upperCase words;

Column names, SQL expressions and variables are ALWAYS lowerCase;

SQL keywords, Table aliases and column values CAN BE upperCase, but must NOT be prefixed with ' #';

Table aliases (must not be prefixed) will always have whiteSpace preceding them until the end of the previous word, which will be a table name.

Column values (must not be prefixed) will either be numerical values or characters enclosed in quotes.

Here is some sample text requiring application of all the above mentioned rules:

update SYBASE_TABLE
set ok = convert(char(10),MB.limit)
from MOVE_BOOKS MB, PEOPLEPLACES PPL
where MB.move_num = PPL.move_num
AND PPL.mot_ind = 'B'
AND PPL.trade_type_ind = 'P'

So far with I've gotten only this far: (not too far...)

(?-i)[[:upper:]]

Any help would be most appreciated. TIA,

MN

That sounds very bad. SQL is so complex. There is the obvious update table, from table, etc. But adding joins will be painful, the other three or four ways of putting table names in SQL, and the 50 billion other SQL command even more so. Do you have a way to find every sql command uniquely? You might want to try and categorize the commands by frequency and complexity. Use automated scripts on the frequent examples and manual on the less frequent ones. — Seth Robertson
– Seth Robertson, Commented May 27, 2011 at 21:07
Do you have a list of all the possible table names? Perhaps retrievable from the information_schema views? You could just search for those specifically instead. TABLE1|TABLE2|TABLE3 — Martin Smith
– Martin Smith, Commented May 28, 2011 at 0:38
@Seth - 'bad...' agreed... your idea is interesting, but it might take me longer than doing it by hand. — Vector
– Vector, Commented May 28, 2011 at 0:54
@Martin - this might be a very good idea will investigate. Tnx — Vector
– Vector, Commented May 28, 2011 at 0:55

Bart Kiers · Accepted Answer · 2011-05-28 08:42:34Z

5

This is not doable with a simple regex-replacement. You will not be able to make a distinction between upper case words that are tables, are string literals or are commented:

update TABLE set x='NOT_A_TABLE' where y='NOT TABLES EITHER' 
-- AND NO TABLES HERE AS WELL

EDIT

You seem to think that determining if a word is inside a string literal or not is easy, then consider SQL like this:

-- a quote: '
update TABLE set x=42 where y=666
-- another quote: '

or

update TABLE set x='not '' A '''' table' where y=666

EDIT II

Okay, I (obsessively) hammered on the fact that a simple regex replacements is not doable. But I didn't offer a (possible) solution yet. What you could do is create some sort of "hybrid-lexer" based on a couple of different regex-es. What you do is scan through the input file and at the start of each character, try to match either a comment, a string literal, a keyword, or a capitalized word. And if none of these 4 previous patterns matched, then just consume a single character and repeat the process.

A little demo in Python:

#!/usr/bin/env python
import re 

input = """
UPDATE SYBASE_TABLE
SET ok = convert(char(10),MB.limit) -- ignore me!
from MOVE_BOOKS MB, PEOPLEPLACES PPL
where MB.move_num = PPL.move_num
-- comment '
AND PPL.mot_ind = 'B '' X'
-- another comment '
AND PPL.trade_type_ind = 'P -- not a comment'
"""

regex = r"""(?xs)          # x = enable inline comments, s = enable DOT-ALL
  (--[^\r\n]*)             # [1] comments
  |                        # OR
  ('(?:''|[^\r\n'])*')     # [2] string literal
  |                        # OR
  (\b(?:AND|UPDATE|SET)\b) # [3] keywords
  |                        # OR
  ([A-Z][A-Z_]*)           # [4] capitalized word
  |                        # OR
  .                        # [5] fall through: matches any char
"""

output = ''

for m in re.finditer(regex, input): 
    # append a `#` if group(4) matched
    if m.group(4): output += '#'
    # append the matched text (any of the groups!)
    output +=  m.group()

# print the adjusted SQL
print output

which produces:

UPDATE #SYBASE_TABLE
SET ok = convert(char(10),#MB.limit) -- ignore me!
from #MOVE_BOOKS #MB, #PEOPLEPLACES #PPL
where #MB.move_num = #PPL.move_num
-- comment '
AND #PPL.mot_ind = 'B '' X'
-- another comment '
AND #PPL.trade_type_ind = 'P -- not a comment'

This may not be the exact output you want, but I'm hoping the script is simple enought for you to adjust to your needs.

Good luck.

edited May 28, 2011 at 8:42

answered May 27, 2011 at 21:17

Bart Kiers

171k38 gold badges307 silver badges297 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

Vector Over a year ago

@Bart - obviously this is no simple regex replacement, but I believe that finding or excluding words in quotes is not among the diffficulties here. Literals must be quoted, table names not.

Bart Kiers Over a year ago

@Mikey, you yourself already said "I'm quite inexperienced with RegEx...", and I consider myself quite familiar with regex. So if you believe there is a regex-based solution, by all means, feel free to wait right here in the hope that someone posts such a solution. I wouldn't hold my breath, if I were you. :)

Bart Kiers Over a year ago

If you were a bit more experienced with regex, you wouldn't have made the remark "I believe that finding or excluding words in quotes is not among the diffficulties here". This is the difficulty here.

Vector Over a year ago

@Bart - I believe there probably is a RegEx solution - not based on any particular knowledge I have but because I think that RegEx can do just about anything when it comes to parsing text. But I won't hold my breath - I think Martin's suggestion will enable me to do the job. Be that as it may, if you are quite familiar with RegEx I must defer to you, but since a quotation mark is a discrete, known symbol with clear rules regarding its placement, I don't see the difficulty - if that was the only problem, I wouldn't need RegEx at all.

Bart Kiers Over a year ago

@Mikey, "I think that RegEx can do just about anything when it comes to parsing text", you are gravely mistaken. If you don't see the difference with regex and a recursive descent parser, you ought to do some reading about them both (no offense meant!). Sure, some regex flavors have the capability to define recursive pattern, but parsing large chunks of SQL reliable with regex is just madness: you can't do it. The fact that no one here on SO suggested some sort of regex is telling.

|

Collectives™ on Stack Overflow

Parsing Transact SQL with RegEx

1 Answer 1

EDIT

EDIT II

13 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

EDIT

EDIT II

13 Comments

Your Answer

Sign up or log in

Post as a guest

Related