Split by keywords

[stuartmarsh]

I'm trying to break up a SQL statement so that I can can work on each segment individually.

This is the code I have so far.

$SQL = "SELECT *
		FROM Table_1
		INNER JOIN Table_2 ON Table_1.ID=Table_2.Table_1_ID
		JOIN Table_3 ON Table_1.ID=Table_3.Table_1_ID
		LEFT OUTER JOIN Table_4 ON Table_3.ID=Table_4.Table_3_ID
		WHERE Field1='BLAH' AND Field2 IN ('1', '2', '3')
		GROUP BY Field1, Field2
		HAVING Field3='CHOO'
		ORDER BY Field1, Field2 ";
	$test = preg_split('/(select|from|(.*)join|where|group by|having|order by)/im', $SQL, -1, PREG_SPLIT_DELIM_CAPTURE|PREG_SPLIT_NO_EMPTY);
	var_dump($test);

This is the result:

array
  0 => string 'SELECT' (length=6)
  1 => string ' *
			' (length=
  2 => string 'FROM' (length=4)
  3 => string ' Table_1
' (length=10)
  4 => string '				INNER JOIN' (length=14)
  5 => string '				INNER ' (length=10)
  6 => string ' Table_2 ON Table_1.ID=Table_2.Table_1_ID
' (length=43)
  7 => string '				JOIN' (length=
  8 => string '				' (length=4)
  9 => string ' Table_3 ON Table_1.ID=Table_3.Table_1_ID
' (length=43)
  10 => string '				LEFT OUTER JOIN' (length=19)
  11 => string '				LEFT OUTER ' (length=15)
  12 => string ' Table_4 ON Table_3.ID=Table_4.Table_3_ID
			' (length=47)
  13 => string 'WHERE' (length=5)
  14 => string ' Field1='BLAH' AND Field2 IN ('1', '2', '3')
			' (length=50)
  15 => string 'GROUP BY' (length=
  16 => string ' Field1, Field2
			' (length=21)
  17 => string 'HAVING' (length=6)
  18 => string ' Field3='CHOO'
			' (length=20)
  19 => string 'ORDER BY' (length=
  20 => string ' Field1, Field2 ' (length=16)

 

As you can see it's sorta doing the right thing but I need it to only split before the keyword, instead of before and after as it is doing.  It is also producing two results when there is an "Inner Join".

 

Can someone point out what I'm doing wrong?

 

Thanks,

Stu

[cags]

Changing the entire pattern into a lookahead assertion would get you a lot closer to what your looking for. The main problem I can see with this will be the different types of JOIN, ie how to deal with LEFT, INNER and OUTER, whilst still letting JOIN work on it's own.

[stuartmarsh]

I think I'm going to include each type of join manually.

If two joins are on the same line it doesn't work properly.

Thanks.

[stuartmarsh]

Still playing around with this.

I have modified my regex to the following

/(?=select|from|(left|right) (inner|outer) join|where|group by|having|order by)/im

Can I make the (left|right) and (inner|outer) optional?

So that it matches JOIN but also matches if there is any combination of left/right/inner/outer before it?

Thanks

[cags]

This is the problem I was alluding to earlier, you can, but I don't think it will work correctly. One problem you will have with what you've currently got is that if there is no inner|outer keyword, it will attempt to match two spaces before JOIN. This is easily fixed by moving the spaces inside the pattern (left |right )(inner |outer )join but I believe that using preg_split this will actually split at, for example left join, then immediately split again at the join. I've not experimented, but I can't think of a solution to this problem. In theory you could use negative lookbehind assertions to only split on JOIN if not preceded by the left|right or inner|outer, but since PHP only supports fixed length look behind assertions you'd have to manually type out the combinations and use alternation on them.