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

Question

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 have no idea how to get this done.

Thanks in advance.

I should also mention that the parts of the sql occur within a complete CREATE TABLE statement. — d1rk
– d1rk, Commented Apr 14, 2011 at 7:22

Trey Hunner · Accepted Answer · 2011-04-14 07:40:12Z

2

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)

edited Apr 14, 2011 at 7:40

answered Apr 14, 2011 at 7:14

Trey Hunner

11.9k4 gold badges58 silver badges125 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

d1rk Over a year ago

Thank you - I forgot to mention that this statement is just an example. The keys (in this case: identifier, keyname) and the name of the constraint (in this case: UNIQUE_TAG) can vary from time to time.

Trey Hunner Over a year ago

Okay. I've generalized the constraint name and keys.

bw_üezi · Accepted Answer · 2011-04-14 09:55:58Z

0

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

edited Apr 14, 2011 at 9:55

answered Apr 14, 2011 at 7:24

bw_üezi

4,6044 gold badges26 silver badges43 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related