Topic: 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.

Code: [Select]

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

Code: [Select]

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

Code: [Select]

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.