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:

  • 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.
  • Security. Before execution, all SQL queries are checked against the user's security role/permissions.  
  • 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 expression provides a syntax for creating pivot tables. See Pivot Queries.
  • User-related functions. USERID() and ISMEMBEROF(groupid) lets you control query visibility based on the user's group membership. 

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 results in ascending value order.

    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 results in descending value order.
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

ORDER BY Often used 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 being ignored?"

When authoring queries in LabKey SQL, ORDER BY clauses may appear to not be respected in the results displayed to the user. This is because a LabKey SQL query is typically processed as a subquery within a parent query, and the parent's sort order overrides the ORDER BY clause in the subquery.

Two recommended solutions:
(1) Define the sort in the parent query using the grid view customizer.
(2) Use the "config.sort" property of the 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 BY Re-visualize a table by rotating or "pivoting" a portion of it, essentially promoting cell data to column headers. See Pivot Queries for examples. Note that Pivot queries cannot be used with compliance logging. For details, see Compliance: Logging.
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. 
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  
 || String concatenation. For example:    
   
    SELECT ParticipantId,
    City || ', ' || State AS CityOfOrigin
    FROM Demographics
 LIKE  
 NOT LIKE  
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

LabKey SQL parser sometimes requires the use of additional parentheses within the statement.

    CASE (value) WHEN (test1) THEN (result1) ELSE (result2) END
    CASE WHEN (test1) THEN (result1) ELSE (result2) 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
 decode(text,
  format)
See the PostgreSQL docs.
 encode(binary,
  format)
See the PostgreSQL docs.
 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.
 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'.
 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.

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(string, 
 searchString,
 index) 
Returns the position of searchString in string, starting the search at index.
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'}

Was this content helpful?

Log in or register an account to provide feedback


previousnext
 
expand allcollapse all