1

Consider the query:

CREATE TABLE "albums" ( [AlbumId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 
[Title] NVARCHAR(160) NOT NULL )

I am trying to extract everything after the table name(albums in this case), i.e. the statement:

( [AlbumId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, [Title] NVARCHAR(160) NOT NULL )

I want to do this because I want to duplicate the tables exactly how they were written at the time of creation. The problem is table names can have parenthesis/special characters in their name like this(also notice no whitespace after table name):

CREATE TABLE "al(b+?u(ms"( [AlbumId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 
[Title] NVARCHAR(160) NOT NULL )

They can also be without the double quotes but then they won't have any special characters like so:

CREATE TABLE albums ( [AlbumId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 
[Title] NVARCHAR(160) NOT NULL )

How do I extract the table info present after the parenthesis after table name with regex in these cases?

Bergi
  • 630,263
  • 148
  • 957
  • 1,375
Divyansh
  • 166
  • 1
  • 10
  • @Wiktor I've added the tags back, as the SQL language (and possibly its sqlite dialect) are relevant for the grammar of the terms to match. – Bergi Aug 15 '20 at 22:39
  • Btw, there might also be a non-regex solution. Have a look at [Copy table structure to new table in sqlite3](https://stackoverflow.com/q/12730390/1048572) – Bergi Aug 15 '20 at 22:42
  • @Bergi I saw that but that only copies the table structure and generalizes column types(like say it converts VARCHAR to TEXT when copying). I want the exact structure with all the type information preserved. – Divyansh Aug 15 '20 at 22:44
  • Are you aware of the `.dump` command of the SQLite commandline tool? See the tag description of the "SQLite" tag you used. https://stackoverflow.com/tags/sqlite/info – Yunnosch Aug 15 '20 at 22:53
  • @Yunnosch I can't use `.dump` command because R does not support interacting with SQLite that way.(I am going to use the regex in an R implementation) – Divyansh Aug 16 '20 at 01:05
  • Please provide many different inputs, showing what should match and what should not. Try to show edge cases, i.e. which at first look like they should match but in fact should not. Also show what you tried and explain how it failed, by giving examples of success and of failure. – Yunnosch Aug 16 '20 at 07:01

0 Answers0