NimbleText

Manipulate text and data with light-weight patterns

The 'Where' Clause

Where clauses are used to filter your input, so that your pattern is only applied to the rows you choose.

Where clauses are only enabled once you purchase a NimbleText license (compare versions). You can turn this feature on using the 'Show where clause' checkbox in the toolbar; it is also available from the Tools menu.

The where clause is a piece of javascript, which is evaluated once for each row of your input data. If it returns true then that row is passed on, and the row is passed on to the pattern. If it returns false, then that row is ignored, and does not affect your output. Note that you don't wrap the where clause in "<% %>" bee-stings, as you do with embedded javascript in patterns.

Example Uses for a Where Clause

Here are some examples of using a where clause.

All of these examples use this input data, concerning the Beatles, and assume the pattern is simply $row

firstName, lastName, instrument, born
John, Lennon, guitar, 1940
Paul, McCartney, bass, 1942
Ringo, Starr, drums, 1940
George, Harrison, guitar, 1943

Skip the header

To skip the header row, use this filter:

$rowNum > 0

The result is...

John, Lennon, guitar, 1940
Paul, McCartney, bass, 1942
Ringo, Starr, drums, 1940
George, Harrison, guitar, 1943

Skip Every Second Row

Or to skip every second row, use javascript's mod operator ('%') 

$rowNum % 2 == 1

The result is...

John, Lennon, guitar, 1940
Ringo, Starr, drums, 1940

Skip Invalid Rows

To only process rows where the fourth column is a number, use the 'is not a number' function, like so:

isNaN($3) == false

The result is...

John, Lennon, guitar, 1940
Paul, McCartney, bass, 1942
Ringo, Starr, drums, 1940
George, Harrison, guitar, 1943

To only use rows where the fourth column is a date, use this filter:

!isNaN(Date.parse($3))

The result is...

John, Lennon, guitar, 1940
Paul, McCartney, bass, 1942
Ringo, Starr, drums, 1940
George, Harrison, guitar, 1943

Simple Character Matching

To only return rows where the first column's first character is 'P', use this filter:

$0[0] == 'P'

The result is...

Paul, McCartney, bass, 1942

To only process rows where the second field ends with 'n', use this filter:

$1.reverse()[0] == 'n'

The result is...

John, Lennon, guitar, 1940
George, Harrison, guitar, 1943

To find rows where the first field has some leading or trailing space, use the trim function. (For this to work you need to turn off the option to trim each field)

$0 != $0.trim()

The result is no data at all, because our input doesn't have leading or trailing space

Regular Expression Matching

The full power (and terror) of regular expressions are at your fingertips.

To only show rows where the letter 'o' appears in the first field:

$0.match(/o/) != null

This gives the result:

John, Lennon, guitar, 1940
Ringo, Starr, drums, 1940
George, Harrison, guitar, 1943

Boolean Logic

You can combine multiple predicate clauses using javascript's And ('&&') and Or ('||') operators.

The And Operator, &&

If we only wanted guitar players born in 1940, we could ask for:

$2 == 'guitar' && $3 == 1940

The result is...

John, Lennon, guitar, 1940

The Or Operator, ||

To skip the header row and shun Ringo at the same time, we could use this filter:

$rowNum > 0 && $2 != 'drums'

The result is...

John, Lennon, guitar, 1940
Paul, McCartney, bass, 1942
George, Harrison, guitar, 1943

To show all the musicians who play stringed instruments we could try this:

$2 == 'guitar' || $2 == 'bass'

And we'd find:

John, Lennon, guitar, 1940
Paul, McCartney, bass, 1942
George, Harrison, guitar, 1943

Where Clause on the Command-Line

You can pass 'where clause' filters via the command-line, with:

     -w, --where=VALUE      Apply a javascript expression to filter rows
     --wherefile=VALUE      Apply a javascript expression from a file to
                            filter rows

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