LabKey SQL 

LabKey SQL is a SQL dialect that supports (1) most standard SQL functionality and (2) provides extended functionality that is unique to LabKey, including:

  • Security. Before execution, all SQL queries are checked against the user's security roles/permissions.
  • Lookup columns. Lookup columns use an intuitive syntax to access data in other tables to achieve what would normally require a JOIN statement. For example: "SomeTable.ForeignKey.FieldFromForeignTable" For details see Lookups. The special lookup column "Datasets" is injected into each study dataset and provides a syntax shortcut when joining the current dataset to another dataset that has compatible join keys. See example usage.
  • Cross-folder querying. Queries can be scoped to folders broader than the current folder and can draw from tables in folders other than the current folder. See Cross-Folder Queries.
  • Parameterized SQL statements. The PARAMETERS keyword lets you define parameters for a query.  An associated API gives you control over the parameterized query from JavaScript code. See Parameterized SQL Queries.
  • Pivot tables. The PIVOT...BY and PIVOT...IN expressions provide a syntax for creating pivot tables. See Pivot Queries.
  • User-related functions. USERID() and ISMEMBEROF(groupid) let you control query visibility based on the user's group membership. 
  • Ontology-related functions. (Premium Feature) Access preferred terms and ontology concepts from SQL queries. See Ontology SQL.
  • Lineage-related functions. (Premium Feature) Access ancestors and descendants of samples and data class entities. See Lineage SQL Queries.
  • Annotations. Override some column metadata using SQL annotations. See Use SQL Annotations.

Reference Tables:


Keywords

Keyword Description
AS Aliases can be explicitly named using the AS keyword. Note that the AS keyword is optional: the following select clauses both create an alias called "Name":

    SELECT LCASE(FirstName) AS Name
    SELECT LCASE(FirstName) Name

Implicit aliases are automatically generated for expressions in the SELECT list.  In the query below, an output column named "Expression1" is automatically created for the expression "LCASE(FirstName)":

    SELECT LCASE(FirstName)
    FROM PEOPLE

ASCENDING, ASC Return ORDER BY results in ascending value order. See the ORDER BY section for troubleshooting notes.

    ORDER BY Weight ASC 
CAST(AS) CAST(R.d AS VARCHAR)

Defined valid datatype keywords which can be used as cast/convert targets, and to what java.sql.Types name each keyword maps. Keywords are case-insensitive.

    BIGINT
    BINARY
    BIT
    CHAR
    DECIMAL
    DATE
    DOUBLE
    FLOAT
    GUID
    INTEGER
    LONGVARBINARY
    LONGVARCHAR
    NUMERIC
    REAL
    SMALLINT
    TIME
    TIMESTAMP
    TINYINT
    VARBINARY
    VARCHAR

Examples:

CAST(TimeCreated AS DATE)

CAST(WEEK(i.date) as INTEGER) as WeekOfYear,

DESCENDING, DESC Return ORDER BY results in descending value order. See the ORDER BY section for troubleshooting notes.

    ORDER BY Weight DESC 
DISTINCT Return distinct, non duplicate, values.

    SELECT DISTINCT Country
    FROM Demographics 
EXISTS Returns a Boolean value based on a subquery. Returns TRUE if at least one row is returned from the subquery.

The following example returns any plasma samples which have been assayed with a score greater than 80%. Assume that ImmuneScores.Data.SpecimenId is a lookup field (aka foreign key) to Plasma.Name. 

    SELECT Plasma.Name
    FROM Plasma
    WHERE EXISTS
        (SELECT *
        FROM assay.General.ImmuneScores.Data
        WHERE SpecimenId = Plasma.Name
        AND ScorePercent > .8)
FALSE  
FROM The FROM clause in LabKey SQL must contain at least one table. It can also contain JOINs to other tables. Commas are supported in the FROM clause:

    FROM TableA, TableB
    WHERE TableA.x = TableB.x

Nested joins are supported in the FROM clause:

    FROM TableA LEFT JOIN (TableB INNER JOIN TableC ON ...) ON...

To refer to tables in LabKey folders other than the current folder, see Cross-Folder Queries.
GROUP BY Used with aggregate functions to group the results.  Defines the "for each" or "per".  The example below returns the number of records "for each" participant:

    SELECT ParticipantId, COUNT(Created) "Number of Records"
    FROM "Physical Exam"
    GROUP BY ParticipantId

HAVING Used with aggregate functions to limit the results.  The following example returns participants with 10 or more records in the Physical Exam table:

    SELECT ParticipantId, COUNT(Created) "Number of Records"
    FROM "Physical Exam"
    GROUP BY ParticipantId
    HAVING COUNT(Created) > 10

HAVING can be used without a GROUP BY clause, in which case all selected rows are treated as a single group for aggregation purposes.
JOIN,
RIGHT JOIN,
LEFT JOIN,
FULL JOIN,
CROSS JOIN
Example:

    SELECT *
    FROM "Physical Exam"
    FULL JOIN "Lab Results"
    ON "Physical Exam".ParticipantId = "Lab Results".ParticipantId 
LIMIT Limits the number or records returned by the query.  The following example returns the 10 most recent records:

    SELECT *
    FROM "Physical Exam"
    ORDER BY Created DESC LIMIT 10

NULLIF(A,B)  Returns NULL if A=B, otherwise returns A.
ORDER BY One option for sorting query results. It may produce unexpected results when dataregions or views also have sorting applied, or when using an expression in the ORDER BY clause, including an expression like table.columnName. If you can instead use a sort on the custom view or via, the API, those methods are preferred (see Troubleshooting note below).

For best ORDER BY results, be sure to a) SELECT the columns on which you are sorting, b) sort on the SELECT column, not on an expression. To sort on an expression, include the expression in the SELECT (hidden if desired) and sort by the alias of the expression. For example:

    SELECT A, B, A+B AS C @hidden ... ORDER BY C
...is preferable to:
    SELECT A, B ... ORDER BY A+B

Use ORDER BY with LIMIT to improve performance:

    SELECT ParticipantID,
    Height_cm AS Height
    FROM "Physical Exam"
    ORDER BY Height DESC LIMIT 5

Troubleshooting: "Why is the ORDER BY clause not working as expected?"

1. Check to ensure you are sorting by a SELECT column (preferred) or an alias of an expression. Syntax like including the table name (i.e. ...ORDER BY table.columnName ASC) is an expression and should be aliased in the SELECT statement instead (i.e. SELECT table.columnName AS C ... ORDER BY C

2. When authoring queries in LabKey SQL, the query is typically processed as a subquery within a parent query. This parent query may apply it's own sorting overriding the ORDER BY clause in the subquery. This parent "view layer" provides default behavior like pagination, lookups, etc. but may also unexpectedly apply an additional sort.

Two recommended solutions for more predictable sorting:
(A) Define the sort in the parent query using the grid view customizer. This may involve adding a new named view of that query to use as your parent query.
(B) Use the "sort" property in the selectRows API call.

PARAMETERS Queries can declare parameters using the PARAMETERS keyword. Default values data types are supported as shown below:

    PARAMETERS (X INTEGER DEFAULT 37)
    SELECT *
    FROM "Physical Exam"
    WHERE Temp_C = X

Parameter names will override any unqualified table column with the same name.  Use a table qualification to disambiguate.  In the example below, R.X refers to the column while X refers to the parameter:

    PARAMETERS(X INTEGER DEFAULT 5)
    SELECT *
    FROM Table R
    WHERE R.X = X

Supported data types for parameters are: BIGINT, BIT, CHAR, DECIMAL, DOUBLE, FLOAT, INTEGER, LONGVARCHAR, NUMERIC, REAL, SMALLINT, TIMESTAMP, TINYINT, VARCHAR

Parameter values can be passed via JavaScript API calls to the query. For details see Parameterized SQL Queries.

PIVOT/PIVOT...BY/PIVOT...IN Re-visualize a table by rotating or "pivoting" a portion of it, essentially promoting cell data to column headers. See Pivot Queries for details and examples.
SELECT SELECT queries are the only type of query that can currently be written in LabKey SQL.  Sub-selects are allowed both as an expression, and in the FROM clause.

Aliases are automatically generated for expressions after SELECT.  In the query below, an output column named "Expression1" is automatically generated for the expression "LCASE(FirstName)":

    SELECT LCASE(FirstName) FROM...

TRUE  
UNION, UNION ALL The UNION clause is the same as standard SQL.  LabKey SQL supports UNION in subqueries. 
VALUES ... AS A subset of VALUES syntax is supported. Generate a "constant table" by providing a parenthesized list of expressions for each row in the table. The lists must all have the same number of elements and corresponding entries must have compatible data types. For example:

VALUES (1, 'one'), (2, 'two'), (3, 'three') AS t;

You must provide the alias for the result ("AS t" in the above), aliasing column names is not supported. The column names will be 'column1', 'column2', etc.
WHERE Filter the results for certain values. Example:

    SELECT *
    FROM "Physical Exam"
    WHERE YEAR(Date) = 2010 
WITH

Define a "common table expression" which functions like a subquery or inline view table. Especially useful for recursive queries.

Usage Notes: If there are UNION clauses that do not reference the common table expression (CTE) itself, the server interprets them as normal UNIONs. The first subclause of a UNION may not reference the CTE. The CTE may only be referenced once in a FROM clause or JOIN clauses within the UNION. There may be multiple CTEs defined in the WITH. Each may reference the previous CTEs in the WITH. No column specifications are allowed in the WITH (as some SQL versions allow).

Exception Behavior: Testing indicates that PostgreSQL does not provide an exception to LabKey Server for a non-ending, recursive CTE query. This can cause the LabKey Server to wait indefinitely for the query to complete. MS SQL Server does provide an exception to the server which allows the LabKey Server to end the query attempt. 

A non-recursive example:

WITH AllDemo AS
  (
     SELECT *
     FROM "/Studies/Study A/".study.Demographics
     UNION
     SELECT *
     FROM "/Studies/Study B/".study.Demographics
  )
SELECT ParticipantId from AllDemo

A recursive example: In a table that holds parent/child information, this query returns all of the children and grandchildren (recursively down the generations), for a given "Source" parent.

PARAMETERS
(
    Source VARCHAR DEFAULT NULL
)

WITH Derivations AS 
( 
   -- Anchor Query. User enters a 'Source' parent 
   SELECT Item, Parent
   FROM Items 
   WHERE Parent = Source
   UNION ALL 

   -- Recursive Query. Get the children, grandchildren, ... of the source parent
   SELECT i.Item, i.Parent 
   FROM Items i INNER JOIN Derivations p  
   ON i.Parent = p.Item 
) 
SELECT * FROM Derivations

 

Constants

The following constant values can be used in LabKey SQL queries.

Constant Description
CAST('Infinity' AS DOUBLE)  Represents positive infinity.
CAST('-Infinity' AS DOUBLE)  Represents negative infinity.
CAST('NaN' AS DOUBLE)  Represents "Not a number".
TRUE  Boolean value.
FALSE  Boolean value.


Operators

Operator Description
String Operators Note that strings are delimited with single quotes. Double quotes are used for column and table names containing spaces.
 || String concatenation. For example:    
   
    SELECT ParticipantId,
    City || ', ' || State AS CityOfOrigin
    FROM Demographics

If any argument is null, the || operator will return a null string. To handle this, use COALESCE with an empty string as it's second argument, so that the other || arguments will be returned:
    City || ', ' || COALESCE (State, '')
 LIKE Pattern matching. The entire string must match the given pattern. Ex: LIKE 'W%'.
 NOT LIKE Negative pattern matching. Will return values that do not match a given pattern. Ex: NOT LIKE 'W%'
Arithmetic Operators  
 + Add
 - Subtract
 * Multiply
 / Divide
Comparison operators  
 = Equals
 != Does not equal
 <>  Does not equal
 >  Is greater than
 <  Is less than
 >= Is greater than or equal to
 <= Is less than or equal to
 IS NULL Is NULL
 IS NOT NULL Is NOT NULL
 BETWEEN Between two values. Values can be numbers, strings or dates.
 IN Example: WHERE City IN ('Seattle', 'Portland')
 NOT IN Example: WHERE City NOT IN ('Seattle', 'Portland')
Bitwise Operators  
 & Bitwise AND
 | Bitwise OR
 ^ Bitwise exclusive OR
Logical Operators  
 AND Logical AND
 OR Logical OR
 NOT Example: WHERE NOT Country='USA'

Operator Order of Precedence

Order of Precedence Operators
 1  - (unary) , + (unary), CASE
 2  *, / (multiplication, division) 
 3  +, -, & (binary plus, binary minus)
 4  & (bitwise and)
 5  ^ (bitwise xor)
 6  | (bitwise or)
 7  || (concatenation)
 8  <, >, <=, >=, IN, NOT IN, BETWEEN, NOT BETWEEN, LIKE, NOT LIKE 
 9  =, IS, IS NOT, <>, !=  
10  NOT
11  AND
12  OR

Aggregate Functions - General

Function Description
COUNT The special syntax COUNT(*) is supported as of LabKey v9.2.
MIN Minimum
MAX Maximum
AVG Average
SUM Sum 
GROUP_CONCAT An aggregate function, much like MAX, MIN, AVG, COUNT, etc. It can be used wherever the standard aggregate functions can be used, and is subject to the same grouping rules. It will return a string value which is comma-separated list of all of the values for that grouping. A custom separator, instead of the default comma, can be specified. Learn more here. The example below specifies a semi-colon as the separator:

    SELECT Participant, GROUP_CONCAT(DISTINCT Category, ';') AS CATEGORIES FROM SomeSchema.SomeTable

To use a line-break as the separator, use the following:

    SELECT Participant, GROUP_CONCAT(DISTINCT Category, chr(10)) AS CATEGORIES FROM SomeSchema.SomeTable  
stddev(expression) Standard deviation
stddev_pop(expression) Population standard deviation of the input values.
variance(expression) Historical alias for var_samp.
var_pop(expression) Population variance of the input values (square of the population standard deviation).
median(expression) The 50th percentile of the values submitted.

Aggregate Functions - PostgreSQL Only

Function Description
bool_and(expression) Aggregates boolean values. Returns true if all values are true and false if any are false.
bool_or(expression) Aggregates boolean values. Returns true if any values are true and false if all are false.
bit_and(expression) Returns the bitwise AND of all non-null input values, or null if none.
bit_or(expression) Returns the bitwise OR of all non-null input values, or null if none.
every(expression) Equivalent to bool_and(). Returns true if all values are true and false if any are false.
corr(Y,X) Correlation coefficient.
covar_pop(Y,X) Population covariance.
covar_samp(Y,X) Sample covariance.
regr_avgx(Y,X) Average of the independent variable: (SUM(X)/N).
regr_avgy(Y,X) Average of the dependent variable: (SUM(Y)/N).
regr_count(Y,X) Number of non-null input rows.
regr_intercept(Y,X) Y-intercept of the least-squares-fit linear equation determined by the (X,Y) pairs.
regr_r2(Y,X) Square of the correlation coefficient.
regr_slope(Y,X) Slope of the least-squares-fit linear equation determined by the (X,Y) pairs.
regr_sxx(Y,X) Sum of squares of the independent variable.
regr_sxy(Y,X) Sum of products of independent times dependent variable.
regr_syy(Y,X) Sum of squares of the dependent variable.
stddev_samp(expression) Sample standard deviation of the input values.
var_samp(expression) Sample variance of the input values (square of the sample standard deviation).

SQL Functions - General

Many of these functions are similar to standard SQL functions -- see the JBDC escape syntax documentation for additional information.

Function Description
abs(value) Returns the absolute value.
acos(value) Returns the arc cosine.
age(date1, date2)

Supplies the difference in age between the two dates, calculated in years.

age(date1, date2, interval)

The interval indicates the unit of age measurement, either SQL_TSI_MONTH or SQL_TSI_YEAR.

age_in_months(date1, date2) Behavior is undefined if date2 is before date1.
age_in_years(date1, date2) Behavior is undefined if date2 is before date1.
asin(value) Returns the arc sine.
atan(value) Returns the arc tangent.
atan2(value1, value2) Returns the arctangent of the quotient of two values.
case

CASE can be used to test various conditions and return various results based on the test. You can use either simple CASE or searched CASE syntax. In the following examples "value#" indicates a value to match against, where "test#" indicates a boolean expression to evaluate. In the "searched" syntax, the first test expression that evaluates to true will determine which result is returned. Note that the LabKey SQL parser sometimes requires the use of additional parentheses within the statement.

    CASE (value) WHEN (value1) THEN (result1) ELSE (result2) END
    CASE (value) WHEN (value1) THEN (result1) WHEN (value2) THEN (result2) ELSE (resultDefault) END
    CASE WHEN (test1) THEN (result1) ELSE (result2) END
    CASE WHEN (test1) THEN (result1) WHEN (test2) THEN (result2) WHEN (test3) THEN (result3) ELSE (resultDefault) END

Example:

    SELECT "StudentName",
    School,
    CASE WHEN (Division = 'Grades 3-5') THEN (Scores.Score*1.13) ELSE Score END AS AdjustedScore,
    Division
    FROM Scores 

ceiling(value) Rounds the value up.
coalesce(value1,...,valueN) Returns the first non-null value in the argument list. Use to set default values for display.
concat(value1,value2) Concatenates two values. 
contextPath() Returns the context path starting with “/” (e.g. “/labkey”). Returns the empty string if there is no current context path. (Returns VARCHAR.)
cos(radians) Returns the cosine.
cot(radians) Returns the cotangent.
curdate() Returns the current date.
curtime() Returns the current time
dayofmonth(date) Returns the day of the month (1-31) for a given date.
dayofweek(date) Returns the day of the week (1-7) for a given date. (Sun=1 and Sat=7)
dayofyear(date) Returns the day of the year (1-365) for a given date.
degrees(radians) Returns degrees based on the given radians.
exp(n) Returns Euler's number e raised to the nth power. e = 2.71828183 
floor(value) Rounds down to the nearest integer.
folderName() LabKey SQL extension function. Returns the name of the current folder, without beginning or trailing "/". (Returns VARCHAR.)
folderPath() LabKey SQL extension function. Returns the current folder path (starts with “/”, but does not end with “/”). The root returns “/”. (Returns VARCHAR.)
greatest(a, b, c, ...) Returns the greatest value from the list expressions provided. Any number of expressions may be used. The expressions must have the same data type, which will also be the type of the result. The LEAST() function is similar, but returns the smallest value from the list of expressions. GREATEST() and LEAST() are not implemented for SAS databases.

When NULL values appear in the list of expressions, different database implementations as follows:

- PostgreSQL & MS SQL Server ignore NULL values in the arguments, only returning NULL if all arguments are NULL.
- Oracle and MySql return NULL if any one of the arguments are NULL. Best practice: wrap any potentially nullable arguments in coalesce() or ifnull() and determine at the time of usage if NULL should be treated as high or low.

Example:

SELECT greatest(score_1, score_2, score_3) As HIGH_SCORE
FROM MyAssay 

hour(time) Returns the hour for a given date/time.
ifdefined(column_name) IFDEFINED(NAME) allows queries to reference columns that may not be present on a table. Without using IFDEFINED(), LabKey will raise a SQL parse error if the column cannot be resolved. Using IFDEFINED(), a column that cannot be resolved is treated as a NULL value. The IFDEFINED() syntax is useful for writing queries over PIVOT queries or assay tables where columns may be added or removed by an administrator.
ifnull(testValue, defaultValue) If testValue is null, returns the defaultValue.  Example: IFNULL(Units,0)
isequal LabKey SQL extension function. ISEQUAL(a,b) is equivalent to (a=b OR (a IS NULL AND b IS NULL))
ismemberof(groupid) LabKey SQL extension function. Returns true if the current user is a member of the specified group.
javaConstant(fieldName) LabKey SQL extension function. Provides access to public static final variable values.  For details see LabKey SQL Utility Functions.
lcase(string) Convert all characters of a string to lower case.
least(a, b, c, ...) Returns the smallest value from the list expressions provided. For more details, see greatest() above.
left(string, integer) Returns the left side of the string, to the given number of characters. Example: SELECT LEFT('STRINGVALUE',3) returns 'STR'
length(string) Returns the length of the given string.
locate(substring, string) locate(substring, string, startIndex) Returns the location of the first occurrence of substring within string.  startIndex provides a starting position to begin the search. 
log(n) Returns the natural logarithm of n.
log10(n) Base base 10 logarithm on n.
lower(string) Convert all characters of a string to lower case.
ltrim(string) Trims white space characters from the left side of the string. For example: LTRIM('     Trim String')
minute(time) Returns the minute value for the given time. 
mod(dividend, divider) Returns the remainder of the division of dividend by divider.
moduleProperty(module name,  property name)

LabKey SQL extension function. Returns a module property, based on the module and property names. For details see LabKey SQL Utility Functions

month(date) Returns the month value (1-12) of the given date. 
monthname(date) Return the month name of the given date.
now() Returns the system date and time.
overlaps LabKey SQL extension function. Supported only when PostgreSQL is installed as the primary database.    
   
    SELECT OVERLAPS (START1, END1, START2, END2) AS COLUMN1 FROM MYTABLE

The LabKey SQL syntax above is translated into the following PostgreSQL syntax:    
   
    SELECT (START1, END1) OVERLAPS (START2, END2) AS COLUMN1 FROM MYTABLE
pi() Returns the value of Ï€.
power(base, exponent) Returns the base raised to the power of the exponent. For example, power(10,2) returns 100.
quarter(date) Returns the yearly quarter for the given date where the 1st quarter = Jan 1-Mar 31, 2nd quarter = Apr 1-Jun 30, 3rd quarter = Jul 1-Sep 30, 4th quarter = Oct 1-Dec 31
radians(degrees) Returns the radians for the given degrees.
rand(), rand(seed) Returns a random number between 0 and 1.
repeat(string, count) Returns the string repeated the given number of times. SELECT REPEAT('Hello',2) returns 'HelloHello'.
round(value, precision) Rounds the value to the specified number of decimal places. ROUND(43.3432,2) returns 43.34
rtrim(string) Trims white space characters from the right side of the string. For example: RTRIM('Trim String     ')
second(time) Returns the second value for the given time.
sign(value) Returns the sign, positive or negative, for the given value. 
sin(value) Returns the sine for the given value.
startswith(string, prefix) Tests to see if the string starts with the specified prefix. For example, STARTSWITH('12345','2') returns FALSE.
sqrt(value) Returns the square root of the value.
substring(string, start, length) Returns a portion of the string as specified by the start location (1-based) and length (number of characters). For example, substring('SomeString', 1,2) returns the string 'So'.
tan(value)

Returns the tangent of the value.

timestampadd(interval, number_to_add, timestamp)

Adds an interval to the given timestamp value. The interval value must be surrounded by quotes. Possible values for interval:

SQL_TSI_FRAC_SECOND
SQL_TSI_SECOND
SQL_TSI_MINUTE
SQL_TSI_HOUR
SQL_TSI_DAY
SQL_TSI_WEEK
SQL_TSI_MONTH
SQL_TSI_QUARTER
SQL_TSI_YEAR

Example: TIMESTAMPADD('SQL_TSI_QUARTER', 1, "Physical Exam".date) AS NextExam

timestampdiff(interval, timestamp1, timestamp2)

Finds the difference between two timestamp values at a specified interval. The interval must be surrounded by quotes.

Example: TIMESTAMPDIFF('SQL_TSI_DAY', SpecimenEvent.StorageDate, SpecimenEvent.ShipDate)

Note that PostgreSQL does not support the following intervals:

SQL_TSI_FRAC_SECOND
SQL_TSI_WEEK
SQL_TSI_MONTH
SQL_TSI_QUARTER
SQL_TSI_YEAR

As a workaround, use the 'age' functions defined above.

truncate(numeric value, precision) Truncates the numeric value to the precision specified. This is an arithmetic truncation, not a string truncation.
  TRUNCATE(123.4567,1) returns 123.4
  TRUNCATE(123.4567,2) returns 123.45
  TRUNCATE(123.4567,-1) returns 120.0

May require an explict CAST into NUMERIC, as LabKey SQL does not check data types for function arguments.

SELECT
PhysicalExam.Temperature,
TRUNCATE(CAST(Temperature AS NUMERIC),1) as truncTemperature
FROM PhysicalExam
ucase(string), upper(string) Converts all characters to upper case.
userid() LabKey SQL extension function. Returns the userid, an integer, of the logged in user. 
username() LabKey SQL extension function. Returns the current user display name. VARCHAR
version() LabKey SQL extension function. Returns the current schema version of the core module as a numeric with four decimal places. For example: 20.0070
week(date) Returns the week value (1-52) of the given date.
year(date) Return the year of the given date.  Assuming the system date is March 4 2023, then YEAR(NOW()) return 2023.

SQL Functions - PostgreSQL Specific

LabKey SQL supports the following PostgreSQL functions.
See the  PostgreSQL docs for usage details.

 PostgreSQL Function   Docs 
 ascii(value) Returns the ASCII code of the first character of value.   
 btrim(value,
  trimchars)
Removes characters in trimchars from the start and end of string. trimchars defaults to white space.

BTRIM(' trim    ') returns TRIM 
BTRIM('abbatrimabtrimabba', 'ab') returns trimabtrim

 character_length(value),  char_length(value)

Returns the number of characters in value.
 chr(integer_code) Returns the character with the given integer_code.  

CHR(70) returns F
 concat_ws(sep text,
val1 "any" [, val2 "any" [,...]]) -> text
Concatenates all but the first argument, with separators. The first argument is used as the separator string, and should not be NULL. Other NULL arguments are ignored. See the PostgreSQL docs.

concat_ws(',', 'abcde', 2, NULL, 22) → abcde,2,22
 decode(text,
  format)
See the PostgreSQL docs.
 encode(binary,
  format)
See the PostgreSQL docs.
 is_distinct_from(a, b) OR
 is_not_distinct_from(a, b)
Not equal (or equal), treating null like an ordinary value. 
 initcap(string) Converts the first character of each separate word in string to uppercase and the rest to lowercase. 
 lpad(string, 
  int,
  fillchars)
Pads string to length int by prepending characters fillchars
 md5(text) Returns the hex MD5 value of text
 octet_length(string)  Returns the number of bytes in string.
 overlaps See above for syntax details.
 quote_ident(string) Returns string quoted for use as an identifier in an SQL statement. 
 quote_literal(string) Returns string quoted for use as a string literal in an SQL statement.
 regexp_replace  See PostgreSQL docs for details: reference doc, example doc
 repeat(string, int) Repeats string the specified number of times.
 replace(string, 
  matchString, 
  replaceString)
Searches string for matchString and replaces occurrences with replaceString.
 rpad(string, 
  int,
  fillchars)
Pads string to length int by postpending characters fillchars.
 similar_to(A,B,C) String pattern matching using SQL regular expressions. 'A' similar to 'B' escape 'C'. See the PostgreSQL docs.
 split_part(string,
  delmiter,
  int)
Splits string on delimiter and returns fragment number int (starting the count from 1).

SPLIT_PART('mississippi', 'i', 4) returns 'pp'.
 string_to_array See Array Functions in the PostgreSQL docs.
 strpos(string,
  substring)
Returns the position of substring in string. (Count starts from 1.)
 substr(string,
 fromPosition,
 charCount)

Extracts the number of characters specified by charCount from string starting at position fromPosition.

SUBSTR('char_sequence', 5, 2) returns '_s' 

 to_ascii(string,
  encoding) 
Convert string to ASCII from another encoding.
 to_hex(int) Converts int to its hex representation.
 translate(text,
  fromText,
  toText) 
Characters in string matching a character in the fromString set are replaced by the corresponding character in toString.
 to_char See Data Type Formatting Functions in the PostgreSQL docs.
 to_date(textdate,
  format) 
See Data Type Formatting Functions in the PostgreSQL docs. 
 to_timestamp See Data Type Formatting Functions in the PostgreSQL docs.
 to_number See Data Type Formatting Functions in the PostgreSQL docs.
 unnest See Array Functions in the PostgreSQL docs.

JSON and JSONB Operators and Functions

LabKey SQL supports the following PostgreSQL JSON and JSONB operators and functions. Note that LabKey SQL does not natively understand arrays and some other features, but it may still be possible to use the functions that expect them.
See the PostgreSQL docs for usage details.

PostgreSQL Operators and Functions Docs
->, ->>, #>, #>>, @>, <@, ?, ?|, ?&, ||, -, #-
LabKey SQL supports these operators via a pass-through function, json_op. The function's first argument is the operator's first operand. The first second is the operator, passed as a string constant. The function's third argument is the second operand. For example, this Postgres SQL expression:
a_jsonb_column --> 2
can be represented in LabKey SQL as:
json_op(a_jsonb_column, '-->', 2)
parse_json, parse_jsonb Casts a text value to a parsed JSON or JSONB data type. For example,
'{"a":1, "b":null}'::jsonb
or
CAST('{"a":1, "b":null}' AS JSONB)
can be represented in LabKey SQL as:
parse_jsonb('{"a":1, "b":null}')
to_json, to_jsonb Converts a value to the JSON or JSONB data type. Will treat a text value as a single JSON string value
array_to_json Converts an array value to the JSON data type.
row_to_json Converts a scalar (simple value) row to JSON. Note that LabKey SQL does not support the version of this function that will convert an entire table to JSON. Consider using "to_jsonb()" instead.
json_build_array, jsonb_build_array Build a JSON array from the arguments
json_build_object, jsonb_build_object Build a JSON object from the arguments
json_object, jsonb_object Build a JSON object from a text array
json_array_length, jsonb_array_length Return the length of the outermost JSON array
json_each, jsonb_each Expand the outermost JSON object into key/value pairs. Note that LabKey SQL does not support the table version of this function. Usage as a scalar function like this is supported:
SELECT json_each('{"a":"foo", "b":"bar"}') AS Value
json_each_text, jsonb_each_text Expand the outermost JSON object into key/value pairs into text. Note that LabKey SQL does not support the table version of this function. Usage as a scalar function (similar to json_each) is supported.
json_extract_path, jsonb_extract_path Return the JSON value referenced by the path
json_extract_path_text, jsonb_extract_path_text Return the JSON value referenced by the path as text
json_object_keys, jsonb_object_keys Return the keys of the outermost JSON object
json_array_elements, jsonb_array_elements Expand a JSON array into a set of values
json_array_elements_text, jsonb_array_elements_text Expand a JSON array into a set of text values
json_typeof, jsonb_typeof Return the type of the outermost JSON object
json_strip_nulls, jsonb_strip_nulls Remove all null values from a JSON object
jsonb_insert Insert a value within a JSON object at a given path
jsonb_pretty Format a JSON object as indented text
jsonb_set Set the value within a JSON object for a given path. Strict, i.e. returns NULL on NULL input.
jsonb_set_lax Set the value within a JSON object for a given path. Not strict; expects third argument to specify how to treat NULL input (one of 'raise_exception', 'use_json_null', 'delete_key', or 'return_target').
jsonb_path_exists, jsonb_path_exists_tz Checks whether the JSON path returns any item for the specified JSON value. The "_tz" variant is timezone aware.
jsonb_path_match, jsonb_path_match_tz Returns the result of a JSON path predicate check for the specified JSON value. The "_tz" variant is timezone aware.
jsonb_path_query, jsonb_path_query_tz Returns all JSON items returned by the JSON path for the specified JSON value. The "_tz" variant is timezone aware.
jsonb_path_query_array, jsonb_path_query_array_tz Returns as an array, all JSON items returned by the JSON path for the specified JSON value. The "_tz" variant is timezone aware.
jsonb_path_query_first, jsonb_path_query_first_tz Returns the first JSON item returned by the JSON path for the specified JSON value. The "_tz" variant is timezone aware.

SQL Functions - MS SQL Server Specific

LabKey SQL supports the following SQL Server functions.
See the SQL Server docs for usage details.

MS SQL Server Function Description
ascii(value) Returns the ASCII code of the first character of value.  
char(int), chr(int) Returns an character for the specified ascii code int
charindex(expressionToFind, 
 expressionToSearch,
 index) 
Returns the position of expressionToFind in expressionToSearch, starting the search at position index.
concat_ws(sep text,
val1 "any" [, val2 "any" [,...]]) -> text
Concatenates all but the first argument, with separators. The first argument is used as the separator string, and should not be NULL. Other NULL arguments are ignored.

concat_ws(',', 'abcde', 2, NULL, 22) → abcde,2,22
difference(string,string) Returns the difference between the soundex values of two expressions as an integer.
See the MS SQL docs.
isnumeric(expression) Determines whether an expression is a valid numeric type. See the MS SQL docs.
len(string) Returns the number of characters in string. Trailing white space is excluded. 
patindex(pattern,string) Returns the position of the first occurrence of pattern in string. See the MS SQL docs
quotename See the MS SQL docs.

replace(string,pattern, replacement)

Replaces all occurences of pattern with replacement in the string provided. See the MS SQL docs.
replicate(string,int) Replicate string the specified number of times.
reverse(string) Returns the string in reverse character sequence.
right(string,index) Returns the right part of string to the specified index.
soundex See the MS SQL docs.
space(int) Returns a string of white space characters.
str(float,length,decimal) See the MS SQL docs
stuff(string,
 start,
 length,
 replaceWith)
Inserts replaceWith into string. Deletes the specified length of characters in string at the start position and then inserts replaceWith. See the MS SQL docs.

General Syntax

Syntax Item Description
Case Sensitivity Schema names, table names, column names, SQL keywords, function names are case-insensitive in LabKey SQL.
Comments  Comments that use the standard SQL syntax can be included in queries. '--' starts a line comment. Also, '/* */' can surround a comment block:

-- line comment 1
-- line comment 2
/* block comment 1
    block comment 2 */
SELECT ... 

Identifiers Identifiers in LabKey SQL may be quoted using double quotes. (Double quotes within an identifier are escaped with a second double quote.) 

SELECT "Physical Exam".*
... 
Lookups Lookups columns reference data in other tables.  In SQL terms, they are foreign key columns.  See Lookups for details on creating lookup columns. Lookups use a convenient syntax of the form "Table.ForeignKey.FieldFromForeignTable" to achieve what would normally require a JOIN in SQL. Example:

Issues.AssignedTo.DisplayName

String Literals String literals are quoted with single quotes ('). Within a single quoted string, a single quote is escaped with another single quote.

   SELECT * FROM TableName WHERE FieldName = 'Jim''s Item' 

Date/Time Literals

Date and Timestamp (Date&Time) literals can be specified using the JDBC escape syntax

{ts '2001-02-03 04:05:06'}

{d '2001-02-03'}

Related Topics

Was this content helpful?

Log in or register an account to provide feedback


previousnext
 
expand allcollapse all