CQL functions reference list
Overview
The following 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
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
In Progress
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
In Progress
CHARINDEX
Searches for one character expression inside another character string. If found, the function will return the starting position of the first expression.
Supported
In Progress
CHOOSE
Returns an item at the specified index from a list of values in Cinchy.
Supported
In Progress
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
In Progress
CONVERT
Used with CAST to convert an expression of one data type to another.
Supported
In Progress
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
In Progress
DATEDIFF
Returns the count of the specified DATEPART boundaries crossed between the specified STARTDATE and ENDDATE.
Supported
In Progress
DATEDIFF_BIG
Returns the count of the specified DATEPART boundaries crossed between the specified STARTDATE and ENDDATE.
Supported
In Progress
DATENAME
Returns a character string representing the specified DATEPART of the specified date.
Supported
In Progress
DATEPART
Returns an integer representing the specified DATEPART of the specified date.
Supported
In Progress
DATEFROMPARTS
Returns a date value that maps to the specified year, month, and day values.
Supported
In Progress
DATETIMEFROMPARTS
Returns a DATETIME value for the specified date and time arguments.
Supported
In Progress
DATETIME2FROMPARTS
Returns a datetime2 value for the specified date and time arguments.
Supported
In Progress
DATETIMEOFFSETFROMPARTS
Returns a DATETIMEOFFSET value for the specified date and time arguments.
Supported
In Progress
DAY
Returns an integer that represents the day (day of the month) of the specified date.
Supported
In Progress
DIFFERENCE
Returns an integer value measuring the difference between the SOUNDEX () values of two different character expression strings.
Supported
In Progress
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
In Progress
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
In Progress
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
In Progress
IIF
Returns one of two values which is depending on if the Boolean expression evaluates TRUE or FALSE in the Cinchy.
Supported
In Progress
IsValidDetailed (Geometry)
Returns a message that can help to identify problems with a spatial object that isn't valid.
Supported
In Progress
ISDATE
Checks an expression to see if it's correct.
Supported
In Progress
ISJSON
Tests whether a string contains valid JSON.
Supported
In Progress
JSON_MODIFY
Updates the value of a property in a JSON string and returns the updated JSON string.
Supported
In Progress
JSON_QUERY
Extracts an object or an array from a JSON string.
Supported
In Progress
JSON_VALUE
Extracts a scalar value from a JSON string.
Supported
In Progress
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
In Progress
LOG
Returns the natural logarithm of the specified float expression in SQL Server.
Supported
In Progress
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
In Progress
MONTH
Returns an integer that represents the month of the specified date.
Supported
In Progress
PATINDEX
Returns the starting position of the first occurrence of a pattern in a specified expression.
Supported
In Progress
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
In Progress
RAND
Returns a pseudo-random float value from 0 through 1, exclusive.
Supported
In Progress
REDUCE (Geometry)
Returns an approximation of the given geometry instance produced.
Supported
In Progress
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
In Progress
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
In Progress
SOUNDEX
Returns a four-character (SOUNDEX) code to evaluate the similarity of two strings.
Supported
In Progress
SPACE
Returns a string of repeated spaces.
Supported
In Progress
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
In Progress
STAsBinary
Returns the Open Geospatial Consortium (OGC) Well-Known Binary (WKB) representation of a geometry/geography instance.
Supported
In Progress
STAsText
Returns the Open Geospatial Consortium (OGC) Well-Known Text (WKT) representation of a geometry/geography instance.
Supported
In Progress
STBoundary (Geometry)
Returns the boundary of a geometry instance.
Supported
In Progress
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
In Progress
STCentroid
Returns the geometry/geography center of a geometry/geography instance that consists of one or more polygons.
Supported
In Progress
STContains (Geometry)
Returns 1 if a geometry instance completely contains another geometry instance, returns 0 if it doesn't.
Supported
In Progress
STConvexHull (Geometry)
Returns an object representing the convex hull of a geometry instance.
Supported
In Progress
STCrosses (Geometry)
Returns 1 if a geometry instance crosses another geometry instance. Returns 0 if it doesn't.
Supported
In Progress
STCurveToLine (Geometry)
Returns a polygonal approximation of a geometry instance that contains circular arc segments.
Supported
In Progress
STDifference (Geometry)
Returns an object that represents the point set from one geometry instance that doesn't lie within another geometry instance.
Supported
In Progress
STDisjoint (Geometry)
Returns 1 if a geometry instance is spatially disjoint from another geometry instance, returns 0 if it's not.
Supported
In Progress
STDistance
Returns the shortest distance between a point in a geometry/geography instance and a point in another geometry/geography instance.
Supported
In Progress
STEndpoint (Geometry)
Returns the end point of a geometry instance.
Supported
In Progress
STEnvelope (Geometry)
Returns the minimum axis-aligned bounding rectangle of the instance.
Supported
In Progress
STEquals (Geometry)
Returns 1 if a geometry instance represents the same point set as another geometry instance, returns 0 if it doesn't.
Supported
In Progress
STExteriorRing (Geometry)
Returns the exterior ring of a geometry instance that's a Polygon.
Supported
In Progress
STGeometryN (Geometry)
Returns a specified geometry in a geometry collection.
Supported
In Progress
STGeometryType (Geometry)
Returns the Open Geospatial Consortium (OGC) type name represented by geometry instance.
Supported
In Progress
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
In Progress
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
In Progress
STGeomFromWKB
Returns a geometry/geography instance from an Open Geospatial Consortium (OGC) Well-Known Binary (WKB) representation.
Supported
In Progress
STInteriorRingN (Geometry)
Returns the specified interior ring of a Polygon geometry instance.
Supported
In Progress
STIntersection
Returns an object that represents the points where a geometry/geography instance intersects another geometry/geography instance.
Supported
In Progress
STIntersects
Returns 1 if a geometry instance intersects another geometry instance. Returns 0 if it doesn't.
Supported
In Progress
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
In Progress
STIsEmpty (Geometry)
Returns 1 if a geometry instance is empty. Returns 0 if a geometry instance isn't empty.
Supported
In Progress
STIsRing (Geometry)
Returns 1 if a geometry instance fulfills certain requirements.
Supported
In Progress
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
In Progress
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
In Progress
STLength
Returns the total length of the elements in a geometry/geography instance or the geometry/geography instances within a GeometryCollection.
Supported
In Progress
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
In Progress
STLineFromWKB (Geometry)
Returns a geometry LineStringinstance from an Open Geospatial Consortium (OGC) Well-Known Binary (WKB) representation.
Supported
In Progress
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
In Progress
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
In Progress
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
In Progress
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
In Progress
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
In Progress
STNumCurves (Geometry)
Returns the number of curves in a one-dimensional geometry instance.
Supported
In Progress
STNumGeometries (Geometry)
Returns the number of geometries that comprise a geometry instance.
Supported
In Progress
STNumInteriorRing (Geometry)
Returns the number of interior rings of a Polygon geometry instance.
Supported
In Progress
STNumPoints (Geometry)
Returns the sum of the number of points in each of the figures in a geometry instance.
Supported
In Progress
STOverlaps (Geometry)
Returns 1 if a geometry instance overlaps another geometry instance. Returns 0 if it doesn't.
Supported
In Progress
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
In Progress
STPointFromWKB (Geometry)
Returns a geometry Point instance from an Open Geospatial Consortium (OGC) Well-Known Binary (WKB) representation.
Supported
In Progress
STPointN (Geometry)
Returns a specified point in a geometry instance.
Supported
In Progress
STPointOnSurface (Geometry)
Returns an arbitrary point located within the interior of a geometry instance.
Supported
In Progress
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
In Progress
STR
Returns character data converted from numeric data.
Supported
In Progress
STRelate (Geometry)
Returns 1 if a geometry instance is related to another geometry instance, otherwise, returns 0.
Supported
In Progress
STStartPoint (Geometry)
Returns the start point of a geometry instance.
Supported
In Progress
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
In Progress
STTouches (Geometry)
Returns 1 if a geometry instance spatially touches another geometry instance. Returns 0 if it doesn't.
Supported
In Progress
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
In Progress
STUnion (Geometry)
Returns an object that represents the union of a geometry instance with another geometry instance.
Supported
In Progress
STWithin (Geometry)
Returns 1 if a geometry instance is completely within another geometry instance; otherwise, returns 0.
Supported
In Progress
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
In Progress
SYSDATETIME
Returns a datetime2(7) value that contains the date and time of the computer on which the instance is running.
Supported
In Progress
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
In Progress
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
In Progress
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
In Progress
TODATETIMEOFFSET
Returns a DATETIMEOFFSET value that's translated from a datetime2 expression.
Supported
In Progress
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
In Progress
Last updated