regular expression question [closed]

As it currently stands, this question is not a good fit for our Q&A format. We expect answers to be supported by facts, references, or expertise, but this question will likely solicit debate, arguments, polling, or extended discussion. If you feel that this question can be improved and possibly reopened, visit the help center for guidance. Closed 11 years ago.

I have a query like this

SELECT a, (SELECT b FROM w), (SELECT c FROM x) FROM y WHERE a IN (SELECT d FROM z)

and I want to convert it to

SELECT COUNT(*) FROM y WHERE a IN (SELECT d FROM z)

in php. this is a sample and quires are different than this.

Actually the regex must only replace everything between first SELECT and FROM pair with COUNT(*) but do not touch SELECT after that. and it also must consider that there are some oth开发者_JAVA技巧er SELECT FROM pair inside the first pair and replace them too.

I use this now

preg_replace('/SELECT (.*?) FROM/', 'SELECT COUNT(*) AS count FROM', $sql, 1)

but it don't detect the SELECT inside the first SELECT * FROM

---

Let's say you use regex to replace everything between a SELECT and a FROM with COUNT(*). Using a straightforward regex pattern, you can do that in 2 different ways:

1. greedily match everything between SELECT and FROM
2. lazily match everything between SELECT and FROM

Let's take your example input:

SELECT f1, f2, (SELECT f3 FROM t2) FROM t1 WHERE f1 IN (SELECT f4 FROM t)

option 1 would match the following:

SELECT f1, f2, (SELECT f3 FROM t2) FROM t1 WHERE f1 IN (SELECT f4 FROM

(the first SELECT and the last FROM)

and option 2 would match the following:

SELECT f1, f2, (SELECT f3 FROM

(the first SELECT and the first FROM)

Replacing either pattern with something else would break your SQL. Of course there are more "clever" ways to create a pattern to account for this particular SQL input, but I am pretty sure that for every "smart" regex, I (or someone else) will easily find an SQL script that would get "broken" by it.

You might think to use a recursive pattern, but they're a pain to write and maintain, and they too can break SQL code like this:

SELECT ... WHERE x='FOO WHERE BAR' ...

(a literal string containing keywords)

or

SELECT ... WHERE ...     -- FOO WHERE BAR

(a comment containing keywords)

So, my answer to you is: don't use regex for this. Use some sort of SQL parser instead.

---

preg_replace seems like overkill here. Why don't you use str_replace or better, str_ireplace?

$sql = 'SELECT f1, f2, (SELECT f3 FROM t2) FROM t1 WHERE f1 IN (SELECT f4 FROM t)';
$fixed_sql = str_ireplace('f1, f2, (SELECT f3 FROM t2)', 'COUNT(*)', $sql);

If the query is not the same always, you can:

$sql = 'SELECT f1, f2, (SELECT f3 FROM t2) FROM t1 WHERE f1 IN (SELECT f4 FROM t)';
$fixed_sql = 'SELECT COUNT(*) ' . substr($sql, strpos($sql, ' FROM');

(Untested, but the concept is the same).

---

How about:

$sql = preg_replace("/^.*( FROM t1)/", "SELECT COUNT(*)$1", $sql);

---

For the particular pattern you gave:

SELECT ... (...) FROM ...

The following works:

$sql = preg_replace('/^SELECT .*\(.*\) (FROM.*)$/', 'SELECT COUNT(*) $1', $sql);

But as @Bart pointed out, any deviation from this pattern will break your SQL.