Loading TOC...

MarkLogic 10 Product Documentation
sql functions

The table below lists all the sql built-in functions (in this namespace: http://marklogic.com/xdmp/sql).

You can also view these functions broken down by category:

40 functions
Function name Description
sql:bit-length Returns the length of the string "str" in bits.
sql:bucket Returns an unsignedLong specifying the index of the bucket the second parameter belongs to in buckets formed by the first parameter.
sql:collated-string Returns an rdf:collatedString value with the given value and collation tag.
sql:collatedString-collation Returns the collation of an sql:collatedString value.
sql:columnID Returns the column ID from a schema name, a view name and a column name.
sql:dateadd Returns a specified date with the specified number interval (signed integer) added to a specified datepart of that date
sql:datediff Returns the count (signed integer) of the specified datepart boundaries crossed between the specified startdate and enddate.
sql:datepart Returns an integer that represents the specified datepart of the specified date.
sql:day Returns an xs:integer between 1 and 31, both inclusive, representing the day component in the localized value of $arg.
sql:dayname Returns an xs:string representing the dayname value in the localized value of $arg.
sql:difference Returns an integer value that indicates the difference between the SOUNDEX values of two character expressions.
sql:glob Returns true if the specified $input glob the specified $pattern, otherwise returns false.
sql:hours Returns an xs:integer between 0 and 23, both inclusive, representing the value of the hours component in the localized value of $arg.
sql:ifnull If the first expression is NULL, then the value of the second expression is returned.
sql:insert Returns a string that that is the first argument with length characters removed starting at start and the second string has been inserted beginning at start.
sql:instr Find the starting location of a pattern in a string.
sql:left Returns a string that is the leftmost characters of the target string.
sql:like Returns true if the specified $input like the specified $pattern, otherwise returns false.
sql:ltrim Return a string that removes leading empty spaces in the input string.
sql:minutes Returns an xs:integer value between 0 to 59, both inclusive, representing the value of the minutes component in the localized value of $arg.
sql:month Returns an xs:integer between 1 and 12, both inclusive, representing the month component in the localized value of $arg.
sql:monthname Returns month name, calculated from the localized value of $arg.
sql:nullif Returns a NULL value if the two specified values are equal.
sql:octet-length Returns the length of the string "str" in bits.
sql:quarter Returns an xs:integer between 1 and 4, both inclusive, calculating the quarter component in the localized value of $arg.
sql:rand Return a random number.
sql:repeat Returns a string that concatenates the first argument as many times as specified by the second argument.
sql:right Returns a string that is the rightmost characters of the target string.
sql:rtrim Return a string that removes trailing empty spaces in the input string.
sql:seconds Returns an xs:decimal value between 0 and 60.999..., both inclusive, representing the seconds and fractional seconds in the localized value of $arg.
sql:sign Returns the sign of number x.
sql:soundex Returns a four-character (SOUNDEX) code to evaluate the similarity of two strings.
sql:space Returns a string that is the given number of spaces.
sql:strpos Returns an integer value representing the starting position of a string within the search string.
sql:timestampadd Returns a xs:string? timestamp created by adding a number to the given dateTimeType field of a given timestamp.
sql:timestampdiff Returns the difference in dateTimeType field of two given timestamps.
sql:trim Return a string that removes leading and trailing empty spaces in the input string.
sql:week Returns an xs:integer between 1 and 53, both inclusive, representing the week value in the localized value of $arg.
sql:year Returns an xs:integer representing the year component in the localized value of $arg.
sql:yearday Returns an xs:integer between 1 and 366, both inclusive, representing the yearday value in the localized value of $arg.