Regex to extract fields and data types from sql statement

Question

I have this sql statement:

CREATE TABLE [dbo].[User]( [UserId] [int] IDENTITY(1,1) NOT NULL, 

[FirstName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [MiddleName] 

[varchar](50) COLLATE SQL_Latin1_General_CP1_CI_A

What i want is regex code which i can use to get all fields and data type.

So will return something like that:

FirstName varchar

MiddleName varchar

Notes: The sql statement will always have this format. I am using .Net to run this regex

Answer 1

You didn't mention whether the SQL statement is in a string on one line or if it's spanning multiple lines.

Assuming it's on one line, this may fit your request:

Dim input As String = "CREATE TABLE [dbo].[User]( [UserId] [int] IDENTITY(1,1) NOT NULL, " & _
                    "[FirstName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [MiddleName] " & _
                    "[varchar](50) COLLATE SQL_Latin1_General_CP1_CI_A"

For Each m As Match In Regex.Matches(input, "\[(?<Field>\w+)\]\s*\[(?<Type>\w+)\]")
    Console.WriteLine("{0} : {1}", m.Groups("Field").Value, m.Groups("Type").Value)
Next

Answer 2

I don't know anything .NET. In some other worlds, the following could handle the search portion of the operation:

\[(.*?)\][\s\n\r]+\[(.*?)\]\((\d\d)\)

Insert that into the "search" format for a .NET regex (whatever that might be), write your output stuff. If linebreaks can occur midword then this could have problems. Note that the above also pulls the type's length, so it would produce

MiddleName varchar 50

To do without the third backreference, just leave it out of the replace (wasted) or do

\[(.*?)\][\s\n\r]+\[(.*?)\]\(\d\d\)

Lots of fine ways to do it. As usual just make sure you understand the potential variability of the input.