Jump to Overview Aggregate String Numerical Psuedo SQL: This application uses a loose adaptation of the SQL language for processing text-based data. There is no database involved. This is the first incarnation and changes may be introduced at any stage.

Syntax Overview

SELECT *

Select all columns in document.

SELECT $1, $2..

Columns are named $1, $2, $3 and so on, these shortnames are exchangeable with column names.

SELECT columnname₁, columnname₂..

If you want to spell out the whole column name, thats fine, too.

SELECT FUNCTION₁(..), FUNCTION₂(..)..

Some aggregate, string and numeric functions are available. Referer to the list below.

You can use SELECT with a combination of the above.
This is a valid statement SELECT name,$4,sum($2),avg(bonus)...

FROM doc_id

doc_id is unique identifier for the current document.

WHERE columnname₁ clause(s)

Returns only rows that match some certain condition(s)

A regular expression can be used as a pattern in conjuction with LIKE, STARTSWITH, and ENDSWITH. Per default, all matches are performed case-insensitive; both regex and literal strings will match letters by ignoring case. For example, Foo will match "Foo", "foo", or "FoO".

Two clauses must be delimited by either an AND or an OR.
The following operators can be used with WHERE:

LIKE string/regex

NOT LIKE string/regex

Specify whole or partial words to search for a pattern. Apart from the regex support, its behaviour is similar to SQL's LIKE operator.

STARTS WITH string/regex

ENDS WITH string/regex

These two are merely a shortcut for LIKE ^.. and LIKE ..$, so:
name LIKE ckson$ is exchangeable with name ENDS WITH ckson

Relational operators

• Equality: any of EQUALS, EQ, == , =
• Inequality: any of NOT EQUALS, NEQ, !=, <>
• Comparison: You get the usual <= , < , >= , >

BETWEEN lower,upper

BETWEEN is a shorthand for the combination of the relational operators >= and <= to specify integer ranges. BETWEEN behaves strict: no spaces before or after the comma. As an example, to extract all employees between the age of 20 and 32, you would do: SELECT name,age FROM .. WHERE age BETWEEN 20,33 both numbers are inclusive.

ORDER BY column-name / $number / alias [ASC|DESC]

Returns a result set with the rows sorted (alpha) by the field identified by either column name or number. Use DESC for a descending order, or ASC for ascending (default)

ORDERN BY .. ...

To compare according to string numerical value (numeric sort) use ORDERN.

In both cases: The ordering column must be in your SELECT statement.

GROUP BY column-name / $number / alias

The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by exactly ONE column.

LIMIT number

Number of results returned.

ALIAS column AS foo

To change the name of a selected value, be it a column name, function or calculation, use an alias. An example with columns: SELECT nation,bronze+gold+silver AS total FROM ... ORDER BY total DESC You can use the resulting alias with ORDER BY, as seen above. Avoid using anything other than letters and digits in a alias.

Aggregate functions

All the following functions, except COUNT, assume column contains only numerical values, anything else is skipped. The decimal separator is a dot, as in 3.14, and not a comma. A thousands separator is not expected to appear: 3,000 should be 3000. If your columns are preformatted with a comma, despair not. You can remove it thru the options menu (consider replacing them beforehand, though, since this might be a bit slow.)

COUNT(*)

Returns the number of records that match your criteria, if any.

COUNT(column)

Pellentesque habitant morbi tris tique senectus et netus

SUM(column)

Return the sum of all the values in column.

MIN(column)

MIN finds the smallest value in the column named column.

MAX(column)

MAX finds the smallest value in the column named column.

AVG(column)

Returns the average value of column...arithmetic mean..

String functions

SUBSTR(column, start, maxlen)

Returns ..

LENGTH(column)

Returns the length of the column column

TOLOWER(column)

Converts content of column to lowercase. Non-alphabetic characters are left unchanged.

TOUPPER(column)

Converts content of column to uppercase.

Numerical functions

Few arithmetic functions are provided as well:

SQRT(column)

Returns the square root of column.

EXP(column)

The exponential function..

LOG(column)

Returns the natural logarithm column.

SIN(column)

Returns the sine of column, in radians.

COS(column)

Returns the cosine of column, in radians.

   c          matches the non-metacharacter c.
   \c         matches the literal character c.
   .          matches any single character including newline. Alternatives: % or *
   ^          matches the beginning of a string.
   $          matches the end of a string.
   [abc...]   character set: matches any of the characters abc....
   [^abc...]  complemented character set: matches any character except abc....
   a1|a2      alternation: matches either r1 or r2.
   a1a2       concatenation: matches r1, and then r2.

   (...)      Use parentheses for grouping to concatenate regular expressions
              containing the alternation operator, '|'. This regex
              WHERE name LIKE ^(P|S) matches all names that begin
              with either P or S.

   a?      a is optional and matched at most once.
   a*      a will be matched zero or more times.
   a+      a will be matched one or more times.
   a{n}    a is matched exactly n times.
   a{n,}   a is matched n or more times.
   a{,m}   a is matched at most m times.
   a{n,m}  a is matched at least n times, but not more than m times.

   \b      word boundary: used to perform a "whole words only" search.
           Given a name JOE JOHNSON, \bJOHNSON\b matches while \bJOHN\b doesn't.
   \d      digit: matches a single digit (0, 1, 2, ..9)
   \w      alphanumeric:matches any word-constituent character (letter, digit, _)
   \W      matches any character that is not the above.
   \s      whitespace (space, tab, etc.)

   \.      matches a literal dot.
   \\      matches a literal backslash.