CQL Functions Master List

1. Overview

The following living list documents Cinchy Query Language functions. Click on the function name to be taken to its individual documentation page.Please note that some CQL functions are currently "in progress" for support on PGSQL deployments. Please check back regularly for the updated list.

2. CQL Functions

Function Name

Description

TSQL Support

PGSQL Support

ABS

Returns the absolute (positive) value of the specified numeric expression.

Supported

ACOS

Returns the angle, in radians, whose cosine is the specified float expression.

Supported

ASCII

Returns the ASCII code value of the leftmost character of a character expression.‌

Supported

ASIN

Returns the angle, in radians, whose sine is the specified float expression.

Supported

ATAN

Returns the angle, in radians, whose tangent is a specified float expression.

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

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

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

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

COT

Returns the trigonometric cotangent of the specified angle - in radians - in the specified float expression.

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

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

FLOOR

Returns the largest integer less than or equal to the specified numeric expression.

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 is not valid.

Supported

In Progress

ISDATE

Checks an expression to see if it is 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

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

LOWER

Returns a character expression after converting uppercase character data to lowercase.‌

Supported

LTRIM

Returns a character expression after it removes leading blanks.‌

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

Returns the constant value of PI.

Supported

POWER

Returns the value of the specified expression to the specified power.

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

REVERSE

Returns the reverse order of a string value.‌

Supported

RIGHT

Returns the right part of a character string with the specified number of characters.‌

Supported

ROUND

Returns a numeric value, rounded to the specified length or precision.

Supported

RTRIM

Returns a character string after truncating all trailing spaces.‌

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

SIN

Returns the trigonometric sine of the specified angle, in radians, and in an approximate numeric, float, expression.

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

SQRT

Returns the square root of the specified float value.

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 does not.

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 does not.

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 does not 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 is 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 does not.

Supported

In Progress

STExteriorRing (Geometry)

Returns the exterior ring of a geometry instance that is 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 does not.

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 is not closed.

Supported

In Progress

STIsEmpty (Geometry)

Returns 1 if a geometry instance is empty. Returns 0 if a geometry instance is not 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 is not 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 is not 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 does not.

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 does not.

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

SWITCHOFFSET

Returns a datetimeoffset value that is 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

TIMEFROMPARTS

Returns a time value for the specified time and with the specified precision.

Supported

In Progress

TODATETIMEOFFSET

Returns a datetimeoffset value that is translated from a datetime2 expression.

Supported