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:
- How to Generate Insert Statements
- How to Generate Update Statements
- How to Generate SQL 'IN' Statements
- How to List the Columns of a Table, Quickly!
- Extracting a list of columns from a block of text
- How to Generate Views the easy way
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.
- General help
- Symbols and Keywords
- Built-in Functions
- The 'Where' clause
- Date Time formatting
- Date Time parsing
- Command-Line Automation
- SQL Master Class
- HTML Master Class
- Popular Text Manipulations Made Easy «« bookmark this one
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