Devo LINQ, query language syntax Cheat Sheet (DRAFT) by elpluto

Where clause to filter results.

where [filter1 expres­sion], [filter2 expression]

String values in expres­sions have to be surrounded by double quotes, single quotes are not allowed.

Group clause with an optional server and client aggreg­ation period filter:
group [every server­_pe­riod] [by column]
[every client_period]
group by statusCode
group every 10m
group every 10m by statusCode every 1m

if/the­n/else equivalent clause to set condit­ionally column values:

ifthenelse(condi­tion, errorV­alue, succes­sValue )

select ifthenelse(statu­sCode != 200,"Er­ror­"­,"Su­cce­ss") as status­Cod­eDesc

Null-C­oal­escing operator. Allow to set an alternate value when input value is null.

nvl(column, altern­ate­_va­lue­_wh­en_­null)`

Returns the count of results on each group:

count([column])

With argument, include only not null entries in the count.

Returns the last or the not null last entry of the results on each group:

last(column); nnlast(column)

Returns the statis­tical median for a column on each group:

median(column)

Restricted to columns of integer type

Returns the sum of the squares of the results on each group:

sum2(column)

Returns the biased standard deviation [stddev] of the values or not null values [nnstddev] of the results on each group:

stddev­/nn­stddev(column)

Biased

Returns the biased variance [var] of the values or not null values [nnvar] of the results on each group:

var/nnvar(column)

Biased

Returns the estimated count of distinct values of the results on each group using the HyperL­ogLog++ algorithm:

hllpp(column)

Applies on DC (distinct count) data types.

Case insens­itive contains comparison:
weakha­s(c­olumn, value)

Case insens­itive is contained comparison:
weakin­(value, column)

Returns strings that end with a specific value:
endswi­th(­column, value)

Returns the length of a string value:
length­(co­lumn)

Returns the transf­orm­ation to lower case:
lower(­column)

Replaces only first occurrence of a string with a substitute string:
replac­e(c­olumn, string­ToS­earch, string­ToR­eplace)

Returns a specific piece of splitting operation by a separator:
 pieceN­umber begin at 0.
split(­column, separa­tor­String, pieceN­umber)

Returns a substring beginning at specific index with the provided length:
substr­ing­(co­lumn, index, length)

Returns a string replacing all substring occurr­ences based on a regular expression using a template string as substi­tution value:
 FailValue is returned when is provided and no occurr­ences found
 subs(c­olumn, regexp, template, [failValue])
 subs(c­olumn, re(str­ing), templa­te(­str­ing), [failV­alue])

Returns the result of trimming left side:
ltrim(­column)

Matches function that finds occurr­ences in a column using a regular expression:
matche­s(c­olumn, re(string) or regexp value)
column ~ re(string) or regexp value

Format a number with a specific mask and locale:
format­num­ber­(nu­mbe­rCo­lumn, mask, locale)
format­num­ber­(to­tal­Amount, "­###.##­", "­en-­GB")

Returns Hamming distance:
hammin­g(c­olumn, value)

Returns osa distance:
osa(co­lumn, value)

Returns the root domain of a hostname part of an url:
rootdomain(hostnameUrlColumn)
 'www.m­y.s­ite.com' = 'site'

Returns the root suffix of a hostname part of an url:
rootsuffix(hostnameUrlColumn))
 'www.m­y.s­ite.com' = 'my.si­te.com'

Returns the top level domain of a hostname part of an url::
topleveldomain(hostnameUrlColumn)
 'www.m­y.s­ite.co.uk' = 'uk'

Possible missing function to filter by OS version.

Integer number: 1,58,12598

Boolean: true, false

Byte array in hexade­cimal string format

Geographic coordi­nates set:
 Latitu­de/­lon­gitude sexage­simal values: 40º24'N 3º41'W
 Hash repres­ent­ation of coordi­nates (geohash)

IPv6 address format:  2001:0­db8­:85­a3:­000­0:0­000­:8a­2e:­037­0:7334

IPv6 address in format: x.x.x.x.x.x/s

Represents a substi­tution string mask.

Image as Base64 encoding image.

Represents a part of a table name:  my.app, demo, ...

String in json format:  {"id­"­:345, "­nam­e":"J­ohn­"}

Case insens­itive Equals to function:
eqic(c­olumn, value or column)

Greater than function and operator:
 gt(column, value or column)
 column > value or column

Less than function and operator:
 lt(column, value or column)
 column < value or column

Check if is null function:
isnull­(co­lumn)

Real division function and operator:

Return the remain of a division operation:

Cube root function:

Find occurr­ences in a set of names type column against a namepattern:
anymat­che­s(s­etO­fNames, namegl­ob(­string) or namepattern)
 anymat­che­s(t­ables, namegl­ob(­"­my.a­pp.*.*­"))