SQL Query Regex

[Suchy]

I'm having troubles with a regular expressions.

 

What I want is to replace the begining of a SQL query.

ex.

SELECT * FROM employes ...
SELECT name.employes, id.employes FROM employes ...
SELECT name.employes, id.dept, nr.room FROM ...
....

 

I need to replace anything between SELECT and FROM with

SELECT COUNT(*) AS num_of_rows  FROM

 

<?php
	 echo "old query -> " . $query . "<br><br>";

	 $regex = "/^(SELECT|Select|select)([\s]|[a-zA-Z0-9\.\*-_,]|[\s])+[(FROM|From|from)]/";

	 if (preg_match($regex , $query ))
	 	echo "YES<br>";
	else
		echo "NO<br>"	;

	 $query = preg_replace($regex, "SELECT COUNT(*) AS num_of_rows  FROM ", $query);

	echo "new query -> " . $query . "<br><br>";
?>

 

 

How can I modify my regex inorder for this to work ?

 

 

[cags]

You seem to be over complicating the subject somewhat, is there some reason something like this won't work?

 

$output = preg_replace('#^select .* from#i', 'SELECT COUNT(*) AS num_of_rows  FROM', $input);

[Suchy]

Thats it, Thanks !

 

But the problem that I had was with newlines and returns.

 

Ex. To keep the query looking clean I had something like this

SELECT name.employes, birhtdate.employes, 
             building.room,  nr.room,
             name.dept, manager.dept,  id.dept,
            ...
FROM ...

Instead of writing it in a single line.

 

This is how I solved the problem

<?php
$query = str_replace("\r" , "" , $query);
$query = str_replace("\n" , "" , $query);
$query = str_replace("\t" , "" , $query);
$regex = "/^SELECT .* FROM/i";

// $regex = "/^SELECT (.|\n|\s|\t|\r)* FROM/i";              <--- this did not work for me
?>

 

 

Once again, thanks for the shorter regex.

[cags]

If you add the s modifier it will solve the problem of newlines without needing the replacements beforehand.

 

$output = preg_replace('#^select .* from#is', 'SELECT COUNT(*) AS num_of_rows  FROM', $input);

[Suchy]

Does not work for me, but thanks for the hint !

[cags]

The s modifier makes the . match newline characters, so I can't see how it wouldn't work. But nevermind, if you have it working you have it working.