Hi All,

I'm trying to retrieve a type of key value pair from the following string:

CREATE TABLE "BoilerModels" ("ID" INTEGER PRIMARY KEY NOT NULL UNIQUE, "BoilerModelID" INTEGER NOT NULL DEFAULT 0, "BoilerManufacturerID" INTEGER NOT NULL DEFAULT 0, "Model" VARCHAR NOT NULL, "IsDeleted" VARCHAR NOT NULL DEFAULT "N", "LastUpdated" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, "DateCreated" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, "Version" INTEGER NOT NULL DEFAULT 0)

What I want to retrieve is the field name, the type and the default value (if there is one).

So the output would be something like:

ID INTEGER

BoilerModelID INTEGER 0

BoilerManufacturerID INTEGER 0

Model VARCHAR

IsDeleted VARCHAR N

LastUpdated DATETIME CURRENT_TIMESTAMP

DateCreated DATETIME CURRENT_TIMESTAMP

Version INTEGER 0

I've got this far...

(?:, "(.*?)").*(?:DEFAULT "?(\w*)?"?)

 But am now royally stuck!  Can anyone please help?

I would support Sergei's comment about using a separate program/test platform for this. Assuming you choose one that supports the syntax used, you could try:

"([^"]+)"\s+(\w+)(?:\s+(?:primary|key|not|null|default|unique))*(?:\s"?(\w+)"?)?\s*(?:,|\))

and then build the output for each match based on

$1 $2 $3

This provides the output you have specified based on the single example but I have made a number of assumptions including:
- the variable name is always enclosed in double-quotes
- the data type always follows immediately and is a single word
- there are a number of optional keywords that are to be ignored (I've picked up "primary", "key" etc from your example but there may be others)
- the default value is always the last item before either a "," or a ")"
- the default item may be enclosed in double-quotes
- no validation is done on any part (i.e. the default value might be specified with single quotes or start with a double-quote and not have a matching ending quote etc)

This might get you started.

Susan