CQL Functions Reference List
Overview
The following living list documents Cinchy Query Language functions. Select the function name to see its individual documentation page. Please note that some CQL functions are"in progress" for support on PGSQL deployments. Please check back regularly for the updated list.
CQL functions
| Function Name | Description | TSQL Support | PGSQL Support |
|---|---|---|---|
| ABS | Returns the absolute (positive) value of the specified numeric expression. | ✅ Supported | ✅ Supported |
| ACOS | Returns the angle, in radians, whose cosine is the specified float expression. | ✅ Supported | ✅ Supported |
| ASCII | Returns the ASCII code value of the leftmost character of a character expression. | ✅ Supported | ✅ Supported |
| ASIN | Returns the angle, in radians, whose sine is the specified float expression. | ✅ Supported | ✅ Supported |
| ATAN | Returns the angle, in radians, whose tangent is a specified float expression. | ✅ Supported | ✅ Supported |
| ATN2 | Returns the angle, in radians, between the positive x-axis and the ray from the origin to the point (y, x), where x and y are the values of the two specified float expressions. | ✅ Supported | ✅ Supported |
| CAST | Used with CONVERT to convert an expression of one data type to another. | ✅ Supported | ✅ Supported |
| CEILING | Returns the smallest integer greater than, or equal to, the specified numeric expression. | ✅ Supported | ✅ Supported |
| CHAR | Converts an int between 0 to 255 to a character value. | ✅ Supported | ✅ Supported |
| CHARINDEX | Searches for one character expression inside another character string. If found, the function will return the starting position of the first expression. | ✅ Supported | ✅ Supported |
| CHOOSE | Returns an item at the specified index from a list of values in Cinchy. | ✅ Supported | 🚫 Not Supported |
| CONCAT | Concatenates two or more string values one after the other. | ✅ Supported | ✅ Supported |
| CONTAINS | A predicate used in the WHERE clause of a CQL SELECT statement to perform full-text search on full-text indexed columns containing character-based data types. | ✅ Supported | 🚫 Not Supported |
| CONVERT | Used with CAST to convert an expression of one data type to another. | ✅ Supported | ✅ Supported |
| COS | Returns the trigonometric cosine of the specified angle - measured in radians - in the specified expression. | ✅ Supported | ✅ Supported |
| COT | Returns the trigonometric cotangent of the specified angle - in radians - in the specified float expression. | ✅ Supported | ✅ Supported |
| DATEADD | Adds a specified number value to a specified DATEPART of an input date value, and then returns that modified value. | ✅ Supported | ✅ Supported |
| DATEDIFF | Returns the count of the specified DATEPART boundaries crossed between the specified STARTDATE and ENDDATE. | ✅ Supported | ✅ Supported |
| DATEDIFF_BIG | Returns the count of the specified DATEPART boundaries crossed between the specified STARTDATE and ENDDATE. | ✅ Supported | 🚫 Not Supported |
| DATENAME | Returns a character string representing the specified DATEPART of the specified date. | ✅ Supported | 🚫 Not Supported |
| DATEPART | Returns an integer representing the specified DATEPART of the specified date. | ✅ Supported | 🚫 Not Supported |
| DATEFROMPARTS | Returns a date value that maps to the specified year, month, and day values. | ✅ Supported | 🚫 Not Supported |
| DATETIMEFROMPARTS | Returns a DATETIME value for the specified date and time arguments. | ✅ Supported | 🚫 Not Supported |
| DATETIME2FROMPARTS | Returns a datetime2 value for the specified date and time arguments. | ✅ Supported | 🚫 Not Supported |
| DATETIMEOFFSETFROMPARTS | Returns a DATETIMEOFFSET value for the specified date and time arguments. | ✅ Supported | 🚫 Not Supported |
| DAY | Returns an integer that represents the day (day of the month) of the specified date. | ✅ Supported | ✅ Supported |
| DIFFERENCE | Returns an integer value measuring the difference between the SOUNDEX () values of two different character expression strings. | ✅ Supported | 🚫 Not Supported |
| DEGREES | Returns the corresponding angle, in degrees, for an angle specified in radians. | ✅ Supported | ✅ Supported |
| EOMONTH | Returns the last day of the month containing a specified date, with an optional offset. | ✅ Supported | 🚫 Not Supported |
| EXP | Returns the exponential value of the specified float expression. | ✅ Supported | ✅ Supported |
| FLOOR | Returns the largest integer less than or equal to the specified numeric expression. | ✅ Supported | ✅ Supported |
| FORMAT | Returns a value formatted with the specified format. | ✅ Supported | ✅ Supported |
| FREETEXT | Provides the ability to search for a matched term based on the meaning of the terms as opposed to the exact character string. | ✅ Supported | 🚫 Not Supported |
| IIF | Returns one of two values which is depending on if the Boolean expression evaluates TRUE or FALSE in the Cinchy. | ✅ Supported | 🚫 Not Supported |
| IsValidDetailed (Geometry) | Returns a message that can help to identify problems with a spatial object that isn't valid. | ✅ Supported | 🚫 Not Supported |
| ISDATE | Checks an expression to see if it's correct. | ✅ Supported | 🚫 Not Supported |
| ISJSON | Tests whether a string contains valid JSON. | ✅ Supported | 🚫 Not Supported |
| JSON_MODIFY | Updates the value of a property in a JSON string and returns the updated JSON string. | ✅ Supported | 🚫 Not Supported |
| JSON_QUERY | Extracts an object or an array from a JSON string. | ✅ Supported | 🚫 Not Supported |
| JSON_VALUE | Extracts a scalar value from a JSON string. | ✅ Supported | 🚫 Not Supported |
| LEFT | Returns the left part of a character string with the specified number of characters. | ✅ Supported | ✅ Supported |
| LEN | Returns the number of characters of the specified string expression, excluding trailing spaces. | ✅ Supported | ✅ Supported |
| LOG | Returns the natural logarithm of the specified float expression in SQL Server. | ✅ Supported | 🚫 Not Supported |
| LOG10 | Returns the base-10 logarithm of the specified float expression. | ✅ Supported | ✅ Supported |
| LOWER | Returns a character expression after converting uppercase character data to lowercase. | ✅ Supported | ✅ Supported |
| LTRIM | Returns a character expression after it removes leading blanks. | ✅ Supported | ✅ Supported |
| MakeValid (Geometry) | Converts an invalid geometry instance into a geometry instance with a valid Open Geospatial Consortium (OGC) type. | ✅ Supported | 🚫 Not Supported |
| MONTH | Returns an integer that represents the month of the specified date. | ✅ Supported | ✅ Supported |
| PATINDEX | Returns the starting position of the first occurrence of a pattern in a specified expression. | ✅ Supported | ✅ Supported |
| PI | Returns the constant value of PI. | ✅ Supported | ✅ Supported |
| POWER | Returns the value of the specified expression to the specified power. | ✅ Supported | ✅ Supported |
| RADIANS | Returns radians when a numeric expression, in degrees, is entered. | ✅ Supported | 🚫 Not Supported |
| RAND | Returns a pseudo-random float value from 0 through 1, exclusive. | ✅ Supported | 🚫 Not Supported |
| REDUCE (Geometry) | Returns an approximation of the given geometry instance produced. | ✅ Supported | 🚫 Not Supported |
| REPLACE | Replaces all occurrences of a specified string value with another string value. | ✅ Supported | ✅ Supported |
| REVERSE | Returns the reverse order of a string value. | ✅ Supported | ✅ Supported |
| RIGHT | Returns the right part of a character string with the specified number of characters. | ✅ Supported | ✅ Supported |
| ROUND | Returns a numeric value, rounded to the specified length or precision. | ✅ Supported | ✅ Supported |
| RTRIM | Returns a character string after truncating all trailing spaces. | ✅ Supported | ✅ Supported |
| ShortestLineTo (Geometry) | Returns a LineStringinstance (which is the distance between the two geometry instances) with two points that represent the shortest distance between the two geometry instances. | ✅ Supported | 🚫 Not Supported |
| SIGN | Returns the positive (+1), zero (0), or negative (-1) sign of the specified expression. | ✅ Supported | ✅ Supported |
| SIN | Returns the trigonometric sine of the specified angle, in radians, and in an approximate numeric, float, expression. | ✅ Supported | ✅ Supported |
| SMALLDATETIMEFROMPARTS | Returns a SMALLDATETIME value for the specified date and time | ✅ Supported | ✅ Supported |
| SOUNDEX | Returns a four-character (SOUNDEX) code to evaluate the similarity of two strings. | ✅ Supported | 🚫 Not Supported |
| SPACE | Returns a string of repeated spaces. | ✅ Supported | 🚫 Not Supported |
| SQUARE | Returns the square of the specified float value. | ✅ Supported | ✅ Supported |
| SQRT | Returns the square root of the specified float value. | ✅ Supported | ✅ Supported |
| STAREA | Returns the total surface area of a geometry/geography instance. | ✅ Supported | 🚫 Not Supported |
| STAsBinary | Returns the Open Geospatial Consortium (OGC) Well-Known Binary (WKB) representation of a geometry/geography instance. | ✅ Supported | 🚫 Not Supported |
| STAsText | Returns the Open Geospatial Consortium (OGC) Well-Known Text (WKT) representation of a geometry/geography instance. | ✅ Supported | 🚫 Not Supported |
| STBoundary (Geometry) | Returns the boundary of a geometry instance. | ✅ Supported | 🚫 Not Supported |
| STBuffer | Returns a geometric/geography object that represents the union of all points whose distance from a geometry/geography instance is less than or equal to a specified value. | ✅ Supported | 🚫 Not Supported |
| STCentroid | Returns the geometry/geography center of a geometry/geography instance that consists of one or more polygons. | ✅ Supported | 🚫 Not Supported |
| STContains (Geometry) | Returns 1 if a geometry instance completely contains another geometry instance, returns 0 if it doesn't. | ✅ Supported | 🚫 Not Supported |
| STConvexHull (Geometry) | Returns an object representing the convex hull of a geometry instance. | ✅ Supported | 🚫 Not Supported |
| STCrosses (Geometry) | Returns 1 if a geometry instance crosses another geometry instance. Returns 0 if it doesn't. | ✅ Supported | 🚫 Not Supported |
| STCurveToLine (Geometry) | Returns a polygonal approximation of a geometry instance that contains circular arc segments. | ✅ Supported | 🚫 Not Supported |
| STDifference (Geometry) | Returns an object that represents the point set from one geometry instance that doesn't lie within another geometry instance. | ✅ Supported | 🚫 Not Supported |
| STDisjoint (Geometry) | Returns 1 if a geometry instance is spatially disjoint from another geometry instance, returns 0 if it's not. | ✅ Supported | 🚫 Not Supported |
| STDistance | Returns the shortest distance between a point in a geometry/geography instance and a point in another geometry/geography instance. | ✅ Supported | 🚫 Not Supported |
| STEndpoint (Geometry) | Returns the end point of a geometry instance. | ✅ Supported | 🚫 Not Supported |
| STEnvelope (Geometry) | Returns the minimum axis-aligned bounding rectangle of the instance. | ✅ Supported | 🚫 Not Supported |
| STEquals (Geometry) | Returns 1 if a geometry instance represents the same point set as another geometry instance, returns 0 if it doesn't. | ✅ Supported | 🚫 Not Supported |
| STExteriorRing (Geometry) | Returns the exterior ring of a geometry instance that's a Polygon. | ✅ Supported | 🚫 Not Supported |
| STGeometryN (Geometry) | Returns a specified geometry in a geometry collection. | ✅ Supported | 🚫 Not Supported |
| STGeometryType (Geometry) | Returns the Open Geospatial Consortium (OGC) type name represented by geometry instance. | ✅ Supported | 🚫 Not Supported |
| STGeomCollFromText (Geometry) | Augmented with any Z (elevation) and M (measure) values carried by the instance, it returns a geometry instance from an Open Geospatial Consortium (OGC) Well-Known Text (WKT) representation. | ✅ Supported | 🚫 Not Supported |
| STGeomFromText (Geometry) | Augmented with any Z (elevation) and M (measure) values carried by the instance, it returns a geometry/geography instance from an Open Geospatial Consortium (OGC) Well-Known Text (WKT) representation. | ✅ Supported | 🚫 Not Supported |
| STGeomFromWKB | Returns a geometry/geography instance from an Open Geospatial Consortium (OGC) Well-Known Binary (WKB) representation. | ✅ Supported | 🚫 Not Supported |
| STInteriorRingN (Geometry) | Returns the specified interior ring of a Polygon geometry instance. | ✅ Supported | 🚫 Not Supported |
| STIntersection | Returns an object that represents the points where a geometry/geography instance intersects another geometry/geography instance. | ✅ Supported | 🚫 Not Supported |
| STIntersects | Returns 1 if a geometry instance intersects another geometry instance. Returns 0 if it doesn't. | ✅ Supported | 🚫 Not Supported |
| STIsClosed (Geometry) | Returns 1 if the start and end points of the given geometry instance are the same. Returns 1 for GeometryCollection types if each contained geometry instance is closed. Returns 0 if the instance isn't closed. | ✅ Supported | 🚫 Not Supported |
| STIsEmpty (Geometry) | Returns 1 if a geometry instance is empty. Returns 0 if a geometry instance isn't empty. | ✅ Supported | 🚫 Not Supported |
| STIsRing (Geometry) | Returns 1 if a geometry instance fulfills certain requirements. | ✅ Supported | 🚫 Not Supported |
| STIsSimple (Geometry) | Returns 1 if a geometry instance is simple, as defined by the Open Geospatial Consortium (OGC). Returns 0 if a geometry instance isn't simple. | ✅ Supported | 🚫 Not Supported |
| STIsValid (Geometry) | Returns true if a geometry instance is well-formed, based on its Open Geospatial Consortium (OGC) type. Returns false if a geometry instance isn't well-formed. | ✅ Supported | 🚫 Not Supported |
| STLength | Returns the total length of the elements in a geometry/geography instance or the geometry/geography instances within a GeometryCollection. | ✅ Supported | 🚫 Not Supported |
| STLineFromText (Geometry) | Augmented with any Z (elevation) and M (measure) values carried by the instance, returns a geometry instance from an Open Geospatial Consortium (OGC) Well-Known Text (WKT) representation. | ✅ Supported | 🚫 Not Supported |
| STLineFromWKB (Geometry) | Returns a geometry LineStringinstance from an Open Geospatial Consortium (OGC) Well-Known Binary (WKB) representation. | ✅ Supported | 🚫 Not Supported |
| STMLineFromText (Geometry) | Augmented with any Z (elevation) and M (measure) values carried by the instance, returns a geometry instance from an Open Geospatial Consortium (OGC) Well-Known Text (WKT) representation. | ✅ Supported | 🚫 Not Supported |
| STMPointFromText (Geometry) | Augmented with any Z (elevation) and M (measure) values carried by the instance, returns a geometry instance from an Open Geospatial Consortium (OGC) Well-Known Text (WKT) representation. | ✅ Supported | 🚫 Not Supported |
| STMPolyFromText | Augmented with any Z (elevation) and M (measure) values carried by the instance, returns a geometry instance from an Open Geospatial Consortium (OGC) Well-Known Text (WKT) representation. | ✅ Supported | 🚫 Not Supported |
| STMLineFromText (Geometry) | Augmented with any Z (elevation) and M (measure) values carried by the instance, returns a geometry instance from an Open Geospatial Consortium (OGC) Well-Known Text (WKT) representation. | ✅ Supported | 🚫 Not Supported |
| STMPointFrom Text (Geometry) | Augmented with any Z (elevation) and M (measure) values carried by the instance, returns a geometry instance from an Open Geospatial Consortium (OGC) Well-Known Text (WKT) representation. | ✅ Supported | 🚫 Not Supported |
| STNumCurves (Geometry) | Returns the number of curves in a one-dimensional geometry instance. | ✅ Supported | 🚫 Not Supported |
| STNumGeometries (Geometry) | Returns the number of geometries that comprise a geometry instance. | ✅ Supported | 🚫 Not Supported |
| STNumInteriorRing (Geometry) | Returns the number of interior rings of a Polygon geometry instance. | ✅ Supported | 🚫 Not Supported |
| STNumPoints (Geometry) | Returns the sum of the number of points in each of the figures in a geometry instance. | ✅ Supported | 🚫 Not Supported |
| STOverlaps (Geometry) | Returns 1 if a geometry instance overlaps another geometry instance. Returns 0 if it doesn't. | ✅ Supported | 🚫 Not Supported |
| STPointFromText (Geometry) | Augmented with any Z (elevation) and M (measure) values carried by the instance, returns a geometry instance from an Open Geospatial Consortium (OGC) Well-Known Text (WKT) representation. | ✅ Supported | 🚫 Not Supported |
| STPointFromWKB (Geometry) | Returns a geometry Point instance from an Open Geospatial Consortium (OGC) Well-Known Binary (WKB) representation. | ✅ Supported | 🚫 Not Supported |
| STPointN (Geometry) | Returns a specified point in a geometry instance. | ✅ Supported | 🚫 Not Supported |
| STPointOnSurface (Geometry) | Returns an arbitrary point located within the interior of a geometry instance. | ✅ Supported | 🚫 Not Supported |
| STPolyFromText (Geometry) | Augmented with any Z (elevation) and M (measure) values carried by the instance, returns a geometry instance from an Open Geospatial Consortium (OGC) Well-Known Text (WKT) representation. | ✅ Supported | 🚫 Not Supported |
| STR | Returns character data converted from numeric data. | ✅ Supported | 🚫 Not Supported |
| STRelate (Geometry) | Returns 1 if a geometry instance is related to another geometry instance, otherwise, returns 0. | ✅ Supported | 🚫 Not Supported |
| STStartPoint (Geometry) | Returns the start point of a geometry instance. | ✅ Supported | 🚫 Not Supported |
| STSymDifference (Geometry) | Returns an object that represents all points that are either in one geometry instance or another geometry instance, but not those points that lie in both instances. | ✅ Supported | 🚫 Not Supported |
| STTouches (Geometry) | Returns 1 if a geometry instance spatially touches another geometry instance. Returns 0 if it doesn't. | ✅ Supported | 🚫 Not Supported |
| STUFF | Inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position. | ✅ Supported | ✅ Supported |
| STUnion (Geometry) | Returns an object that represents the union of a geometry instance with another geometry instance. | ✅ Supported | 🚫 Not Supported |
| STWithin (Geometry) | Returns 1 if a geometry instance is completely within another geometry instance; otherwise, returns 0. | ✅ Supported | 🚫 Not Supported |
| SUBSTRING | Returns part of a character, binary, text, or image expression in SQL Server. | ✅ Supported | ✅ Supported |
| SWITCHOFFSET | Returns a DATETIMEOFFSET value that's changed from the stored time zone offset to a specified new time zone offset. | ✅ Supported | 🚫 Not Supported |
| SYSDATETIME | Returns a datetime2(7) value that contains the date and time of the computer on which the instance is running. | ✅ Supported | ✅ Supported |
| SYSDATETIMEOFFSET | Returns a DATETIMEOFFSET(7) value that contains the date and time of the computer on which the instance is running. The time zone offset is included. | ✅ Supported | ✅ Supported |
| SYSUTCDATETIME | Returns a datetime2 value that contains the date and time of the computer on which the instance is running. The date and time are returned as UTC time (Coordinated Universal Time). | ✅ Supported | ✅ Supported |
| TAN | Returns the tangent of the input expression. | ✅ Supported | ✅ Supported |
| TIMEFROMPARTS | Returns a time value for the specified time and with the specified precision. | ✅ Supported | ✅ Supported |
| TODATETIMEOFFSET | Returns a DATETIMEOFFSET value that's translated from a datetime2 expression. | ✅ Supported | ✅ Supported |
| UPPER | Returns a character expression with lowercase character data converted to uppercase. | ✅ Supported | ✅ Supported |
| YEAR | Returns an integer that represents the year of the specified date. | ✅ Supported | ✅ Supported |
| STRING_AGG | Concatenates the values of string expressions and places separator values between them. The separator isn't added at the end of string. | ✅ Supported | 🚫 Not Supported |
| OPENJSON | Converts an array of JSON to a rowset. | ✅ Supported | 🚫 Not Supported |