RegEx Query - Replacing spaces within parts of a string

Question

I have a program that goes through SQL statements, identifying component parts with a space. eg: -

SELECT * FROM tblSales WHERE CustomerID=10 AND Year=2011

Would produce the following as separate components: -

"SELECT","*","FROM","tblSales","WHERE","CustomerID=10","AND" and "Year=2011"

The problem I have however is the use of spaces within values eg.:-

SELECT * FROM tblSales WHERE CustomerNameID='Test Company' AND Year=2011

Using the same space-separating logic, this would produce components of: -

"SELECT,"*","FROM","tblSales","WHERE","CustomerID='Test","Company'","AND" and "Year=2011".

ie the space between "Test" & "Company" causes it to be treated as two separate components.

Question is, Is it possible via RegEx.Replace to replace the spaces between the quotation marks with another character whilst leaving all other spaces in the string in tact?

Shaun.

Answer to teh question is YES. :).. See this (stackoverflow.com/questions/7796904/regex-for-matching-literal-strings/7797006) then give it a try and post some examples that you have tried out, i.e. show some effort. — Kashyap
– Kashyap, Commented Oct 24, 2011 at 14:36

Tim Pietzcker · Accepted Answer · 2011-10-24 14:37:02Z

1

Assuming you don't have any escaped quotes, you could simply check if the space you're looking at is followed by an even number of quotes (which means it's outside of a string).

So

splitArray = Regex.Split(subjectString, " (?=(?:[^']*'[^']*')*[^']*$)");

gives you the desired result.

As a verbose regex:

[ ]        # Match a space
(?=        # only if it's followed by...
 (?:       # This subregex:
  [^']*'   #  any number of non-quote characters, followed by a quote
  [^']*'   #  same again (to ensure even number of quotes)
 )*        # zero or more times.
 [^']*     # and any number of non-quote characters until...
 $         # the end of the string.
)          # End of lookahead.

answered Oct 24, 2011 at 14:37

Tim Pietzcker

337k59 gold badges520 silver badges572 bronze badges

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

4 Comments

e.dan Over a year ago

Seems to me that assuming there aren't escaped quotes is a recipe for disaster...

Tim Pietzcker Over a year ago

@e.dan: You're certainly right. Those can be handled, too, but that would make the regex more complicated.

e.dan Over a year ago

I think it borders on impossible, and the OP would be advised to read the famous HTML parsing with regex. SQL is arguably at least as complex as HTML, if not more so.

user1011024 Over a year ago

Given the context by which the SQL commands were written then there won't be any escaped characters and so this will work fine. Thanks

Community · Accepted Answer · 2017-05-23 09:59:47Z

1

Possible? Maybe. Maybe not. Certainly not trivial. Parsing SQL with regexes is almost as bad, or quite possibly worse, than parsing HTML with regexes...

You might want to poke around SO and look for other people who have tried to parse SQL...

edited May 23, 2017 at 9:59

CommunityBot

11 silver badge

answered Oct 24, 2011 at 14:46

e.dan

7,5471 gold badge28 silver badges32 bronze badges

Collectives™ on Stack Overflow

RegEx Query - Replacing spaces within parts of a string

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related