Regex for converting mysql sql syntax to sqlite compatible syntax (easy case)

i want to convert the following sql query:

'UNIQUE KEY `UNIQUE_TAG` (`identifier`,`keyname`)'

into this:

'CONSTRAINT `UNIQUE_TAG` UNIQUE (`identifier`,`keyname`)'

My problem is, i have to grab the first keyword as a var, like this:

'<$1> KEY `UNIQUE_TAG` (`identifier`,`keyname`)'

to get it here

'CONSTRAINT `UNIQUE_TAG` <$1> (`identifier`,`keyname`)'

Have a look at http://www.sqlite.org/lang_createtable.html to see the syntax of sqlite compatible SQL.

I want to do it in PHP, but i hav开发者_运维百科e no idea how to get this done.

Thanks in advance.

---

Your syntax wasn't far off.

This example should help:

$query = "UNIQUE KEY `UNIQUE_TAG` (`identifier`,`keyname`)";

$regex = "/\b([^ ]+) KEY ([^ ]+) \(([^)]+)\)/";
$substitution = "CONSTRAINT $2 $1 ($3)";

$results = preg_replace($regex, $substitution, $query);

print $results;

Some explanation:

• \b matches a word boundary (beginning of the string or whitespace)
• [^ ] matches non-space characters and [^ ]+ matches non-space characters one or more times
• (...) captures whatever ... is. This captured group can be referenced in the substitution string by $1 (since it's the first capture group)
• [^)]+ matches a string of non-closing parenthesis characters that is at least one character long

Less readable but more generalized matching:

$regex = "/\b(\w+)\h+KEY\h+(\H+)\h+\(([^)]+)\)/";

Further explanation:

• \w+ matches one or more "word" characters
• \h+ matches one or more horizontal whitespace characters (tabs and spaces)
• \H matches one or more non-"horizontal whitespace" characters (backticks aren't word characters)

---

Try this regex (UNIQUE )?KEY (.+) (\(.+\)) and replace with CONSTRAINT $2 $1$3

<?php
$sourcestring="UNIQUE KEY `UNIQUE_TAG` (`identifier`,`keyname`)";
echo preg_replace('/(UNIQUE )?KEY (.+) (\(.+\))/','CONSTRAINT $2 $1$3',$sourcestring);
?>

http://www.myregextester.com/?r=83cd23ec