NimbleText

Manipulate text and data with light-weight patterns

Extracting a list of columns from a block of text

Sometimes you have a list of the columns, but not in the format you need them.

For example a create table statement contains a list of the columns -- though it takes some work to extract them from there.

If we paste this into NimbleText as data:

CREATE TABLE [dbo].[Products](
    [ProductID] [int] NOT NULL,
    [ProductName] [varchar](100) NOT NULL,
    [CategoryID] [int] NULL,
PRIMARY KEY CLUSTERED

...and tell nimbleText that our data is using a space as the column separator, we can give it this simple pattern:

try it → 
$0

And extract this result, which is basically a list of the columns:

CREATE
[ProductID]
[ProductName]
[CategoryID]
PRIMARY

Now if any of the column names contained spaces we would not have gotten the right value. So let's see what happens with this table definition:

try it → 
CREATE TABLE [dbo].[Products](
    [Product ID] [int] NOT NULL,
    [Product Name] [varchar](100) NOT NULL,
    [Category ID] [int] NULL,
PRIMARY KEY CLUSTERED

when we try to use the same pattern ($0) to extract the column names we get:

CREATE
[Product
[Product
[Category
PRIMARY

So the trick here is to instead tell NimbleText that the column separator is a closing square bracket... ']' -- then we get:

try it → 
CREATE TABLE [dbo
[Product ID
[Product Name
[Category ID
PRIMARY KEY CLUSTERED

Which is much closer to our answer.

We can refine the pattern a little, by skipping the first and last rows, and putting the closing square backet on the end of each row:

try it → 
$each+
$0]

Now the result is much nicer:

[Product ID]
[Product Name]
[Category ID]

This is just a taste of extracting lists out of text. You can think of NimbleText as a very minimalist tool for transforming data.

Why are we doing this in the first place? No doubt we have some purpose in getting the list of columns. We might want to generate an insert statement, an update statement, a stored procedure, a view, or manipulate the text in some other way. No doubt, getting the list of columns is not an end in itself.

Once you have the list of columns, the simplest way to manipulate it is with NimbleText.

We've also written guides on many other ad-hoc queries you can generate:

Further help

You can also get general help, help on all the symbols and keywords, or on the built-in functions, filtering with a where clause, help with the powerful command-line automation, or applying custom formats to your dates and times.

You need to purchase a license to unlock all the features in NimbleText.

If you haven't downloaded NimbleText yet, then for added power, privacy and versatility I sincerely think you should download it now.

Download NimbleText