Namespace: sql

planBuilder. sql

Builds expressions to call functions in the sql server library for a row pipeline.
Since:
  • 2.1.1

Methods


bitLength(str) → {XsInteger}

Returns the length of the string "str" in bits. Provides a client interface to a server function. See sql.bitLength
Parameters:
Name Type Argument Description
str XsString <optional>
The string to be evaluated.
Since:
  • 2.1.1
Returns:
Type
XsInteger

collatedString(string, collationURI) → {XsString}

Returns an rdf:collatedString value with the given value and collation tag. The rdf:collatedString type extends xs:string , and represents a collation tagged string in RDF. Provides a client interface to a server function. See sql.collatedString
Parameters:
Name Type Argument Description
string XsString <optional>
The lexical value.
collationURI XsString <optional>
The collation URI.
Since:
  • 2.1.1
Returns:
Type
XsString

dateadd(datepart, number, date) → {Item}

Returns a specified date with the specified number interval (signed integer) added to a specified datepart of that date Provides a client interface to a server function. See sql.dateadd
Parameters:
Name Type Argument Description
datepart XsString <optional>
Is the part of date where the number will be added. The following table lists all valid datepart arguments. User-defined variable equivalents are not valid. The return data type is the data type of the date argument. Options: datepart parameter abbreviation includes: "year","yyyy","yy" The year part of the date "quarter","qq","q" The quarter part of the date "month","mm","m" The month part of the date "dayofyear","dy","y" The day of the year from the date "day","dd","d" The day of the month from the date "week","wk","ww" The week of the year from the date "weekday","dw" The day of the week from the date "hour","hh" The hour of the day from the date "minute","mi","n" The minute of the hour from the date "second","ss","s" The second of the minute from the date "millisecond","ms" The millisecond of the minute from the date "microsecond","msc" The microsecond of the minute from the date "nanosecond","ns" The nanosecond of the minute from the date
number XsInt <optional>
This number will be added to the datepart of the given date.
date Item <optional>
Is an expression that can be resolved to a time, date or datetime, value. date can be an expression, column expression, user-defined variable or string literal. startdate is subtracted from enddate.
Since:
  • 2.1.1
Returns:
Type
Item

datediff(datepart, startdate, enddate) → {XsInteger}

Returns the count (signed integer) of the specified datepart boundaries crossed between the specified startdate and enddate. Provides a client interface to a server function. See sql.datediff
Parameters:
Name Type Argument Description
datepart XsString <optional>
Is the part of startdate and enddate that specifies the type of boundary crossed. The following table lists all valid datepart arguments. User-defined variable equivalents are not valid. Options: datepart parameter abbreviation includes: "year","yyyy","yy" The year part of the date "quarter","qq","q" The quarter part of the date "month","mm","m" The month part of the date "dayofyear","dy","y" The day of the year from the date "day","dd","d" The day of the month from the date "week","wk","ww" The week of the year from the date "weekday","dw" The day of the week from the date "hour","hh" The hour of the day from the date "minute","mi","n" The minute of the hour from the date "second","ss","s" The second of the minute from the date "millisecond","ms" The millisecond of the minute from the date "microsecond","msc" The microsecond of the minute from the date "nanosecond","ns" The nanosecond of the minute from the date
startdate Item <optional>
Is an expression that can be resolved to a time, date, datetime or value. date can be an expression, column expression, user-defined variable or string literal. startdate is subtracted from enddate.
enddate Item <optional>
Same as startdate.
Since:
  • 2.1.1
Returns:
Type
XsInteger

datepart(datepart, date) → {XsInteger}

Returns an integer that represents the specified datepart of the specified date. Provides a client interface to a server function. See sql.datepart
Parameters:
Name Type Argument Description
datepart XsString <optional>
The part of date that to be returned. Options: datepart parameter abbreviation includes: "year","yyyy","yy" The year part of the date "quarter","qq","q" The quarter part of the date "month","mm","m" The month part of the date "dayofyear","dy","y" The day of the year from the date "day","dd","d" The day of the month from the date "week","wk","ww" The week of the year from the date "weekday","dw" The day of the week from the date "hour","hh" The hour of the day from the date "minute","mi","n" The minute of the hour from the date "second","ss","s" The second of the minute from the date "millisecond","ms" The millisecond of the minute from the date "microsecond","msc" The microsecond of the minute from the date "nanosecond","ns" The nanosecond of the minute from the date "TZoffset","tz" The timezone offset from the date
date Item <optional>
Is an expression that can be resolved to a xs:date, xs:time, xs:dateTime. date can be an expression, column expression,user-defined variable, or string literal.
Since:
  • 2.1.1
Returns:
Type
XsInteger

day(arg) → {XsInteger}

Returns an xs:integer between 1 and 31, both inclusive, representing the day component in the localized value of arg. Provides a client interface to a server function. See sql.day
Parameters:
Name Type Argument Description
arg Item <optional>
The xs:genericDateTimeArg whose day component will be returned.
Since:
  • 2.1.1
Returns:
Type
XsInteger

dayname(arg) → {XsString}

Returns an xs:string representing the dayname value in the localized value of arg. Provides a client interface to a server function. See sql.dayname
Parameters:
Name Type Argument Description
arg Item <optional>
The date whose dayname value will be returned.
Since:
  • 2.1.1
Returns:
Type
XsString

hours(arg) → {XsInteger}

Returns an xs:integer between 0 and 23, both inclusive, representing the value of the hours component in the localized value of arg. Provides a client interface to a server function. See sql.hours
Parameters:
Name Type Argument Description
arg Item <optional>
The genericDateTime whose hours component will be returned.
Since:
  • 2.1.1
Returns:
Type
XsInteger

insert(str, start, length, str2) → {XsString}

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. Provides a client interface to a server function. See sql.insert
Parameters:
Name Type Argument Description
str XsString <optional>
The string to manipulate.
start XsNumeric <optional>
The starting position where characters will be inserted.
length XsNumeric <optional>
The number of characters to be removed.
str2 XsString <optional>
The string to insert.
Since:
  • 2.1.1
Returns:
Type
XsString

instr(str, n) → {XsUnsignedInt}

Find the starting location of a pattern in a string. Provides a client interface to a server function. See sql.instr
Parameters:
Name Type Argument Description
str XsString <optional>
The string to be evaluated.
n XsString <optional>
The pattern to be evaluated.
Since:
  • 2.1.1
Returns:
Type
XsUnsignedInt

left(str, n) → {XsString}

Returns a string that is the leftmost characters of the target string. The number of characters to return is specified by the second argument. Provides a client interface to a server function. See sql.left
Parameters:
Name Type Argument Description
str Item <optional>
The base string. If the value is not a string, its string value will be used.
n XsNumeric <optional>
The number of leftmost characters of the string to return.
Since:
  • 2.1.1
Returns:
Type
XsString

ltrim(str) → {XsString}

Return a string that removes leading empty spaces in the input string. Provides a client interface to a server function. See sql.ltrim
Parameters:
Name Type Argument Description
str XsString <optional>
The string to be evaluated.
Since:
  • 2.1.1
Returns:
Type
XsString

minutes(arg) → {XsInteger}

Returns an xs:integer value between 0 to 59, both inclusive, representing the value of the minutes component in the localized value of arg. Provides a client interface to a server function. See sql.minutes
Parameters:
Name Type Argument Description
arg Item <optional>
The genericDateTime whose minutes component will be returned.
Since:
  • 2.1.1
Returns:
Type
XsInteger

month(arg) → {XsInteger}

Returns an xs:integer between 1 and 12, both inclusive, representing the month component in the localized value of arg. Provides a client interface to a server function. See sql.month
Parameters:
Name Type Argument Description
arg Item <optional>
The genericDateTime whose month component will be returned.
Since:
  • 2.1.1
Returns:
Type
XsInteger

monthname(arg) → {XsString}

Returns month name, calculated from the localized value of arg. Provides a client interface to a server function. See sql.monthname
Parameters:
Name Type Argument Description
arg Item <optional>
The date whose month-name will be returned.
Since:
  • 2.1.1
Returns:
Type
XsString

octetLength(x) → {XsInteger}

Returns the length of the string "str" in bits. Provides a client interface to a server function. See sql.octetLength
Parameters:
Name Type Argument Description
x XsString <optional>
The string to be evaluated.
Since:
  • 2.1.1
Returns:
Type
XsInteger

quarter(arg) → {XsInteger}

Returns an xs:integer between 1 and 4, both inclusive, calculating the quarter component in the localized value of arg. Provides a client interface to a server function. See sql.quarter
Parameters:
Name Type Argument Description
arg Item <optional>
The genericDateTime whose quarter component will be returned.
Since:
  • 2.1.1
Returns:
Type
XsInteger

rand(n) → {XsUnsignedLong}

Return a random number. This differs from xdmp:random in that the argument is a seed. Provides a client interface to a server function. See sql.rand
Parameters:
Name Type Argument Description
n XsUnsignedLong <optional>
The random seed. Currently this parameter is ignored.
Since:
  • 2.1.1
Returns:
Type
XsUnsignedLong

repeat(str, n) → {XsString}

Returns a string that concatenates the first argument as many times as specified by the second argument. Provides a client interface to a server function. See sql.repeat
Parameters:
Name Type Argument Description
str Item <optional>
The string to duplicate. If the value is not a string, its string value will be used.
n XsNumeric <optional>
The number of times to repeat the string.
Since:
  • 2.1.1
Returns:
Type
XsString

Returns a string that is the rightmost characters of the target string. The number of characters to return is specified by the second argument. Provides a client interface to a server function. See sql.right
Parameters:
Name Type Argument Description
str Item <optional>
The base string. If the value is not a string, its string value will be used.
n XsNumeric <optional>
The number of rightmost characters of the string to return.
Since:
  • 2.1.1
Returns:
Type
XsString

rtrim(str) → {XsString}

Return a string that removes trailing empty spaces in the input string. Provides a client interface to a server function. See sql.rtrim
Parameters:
Name Type Argument Description
str XsString <optional>
The string to be evaluated.
Since:
  • 2.1.1
Returns:
Type
XsString

seconds(arg) → {XsDecimal}

Returns an xs:decimal value between 0 and 60.999..., both inclusive, representing the seconds and fractional seconds in the localized value of arg. Note that the value can be greater than 60 seconds to accommodate occasional leap seconds used to keep human time synchronized with the rotation of the planet. Provides a client interface to a server function. See sql.seconds
Parameters:
Name Type Argument Description
arg Item <optional>
The time whose seconds component will be returned.
Since:
  • 2.1.1
Returns:
Type
XsDecimal

sign(x) → {Item}

Returns the sign of number x. Provides a client interface to a server function. See sql.sign
Parameters:
Name Type Argument Description
x XsNumeric <optional>
The number to be evaluated.
Since:
  • 2.1.1
Returns:
Type
Item

space(n) → {XsString}

Returns a string that is the given number of spaces. Provides a client interface to a server function. See sql.space
Parameters:
Name Type Argument Description
n XsNumeric <optional>
The number of spaces to return as a string.
Since:
  • 2.1.1
Returns:
Type
XsString

timestampadd(dateTimeType, value, timestamp) → {Item}

Returns a xs:string? timestamp created by adding a number to the given dateTimeType field of a given timestamp. Provides a client interface to a server function. See sql.timestampadd
Parameters:
Name Type Argument Description
dateTimeType XsString <optional>
The dateTimeType of the timestamp where addition should take place. Available types are: SQL_TSI_FRAC_SECOND nano seconds SQL_TSI_SECOND seconds SQL_TSI_MINUTE minute SQL_TSI_HOUR hour SQL_TSI_DAY day SQL_TSI_WEEK week SQL_TSI_MONTH month SQL_TSI_QUARTER quarter SQL_TSI_YEAR year
value XsInt <optional>
The integer to add to the given dateTimeType field of the third parameter.
timestamp Item <optional>
The xs:dateTime timestamp to which addition has to take place.
Since:
  • 2.1.1
Returns:
Type
Item

timestampdiff(dateTimeType, timestamp1, timestamp2) → {XsInteger}

Returns the difference in dateTimeType field of two given timestamps. Provides a client interface to a server function. See sql.timestampdiff
Parameters:
Name Type Argument Description
dateTimeType XsString <optional>
The dateTimeType of the timestamp where addition should take place. Available types are: SQL_TSI_FRAC_SECOND nano seconds SQL_TSI_SECOND seconds SQL_TSI_MINUTE minute SQL_TSI_HOUR hour SQL_TSI_DAY day SQL_TSI_WEEK week SQL_TSI_MONTH month SQL_TSI_QUARTER quarter SQL_TSI_YEAR year
timestamp1 Item <optional>
The integer to add to the given dateTimeType field of the third parameter.
timestamp2 Item <optional>
The xs:dateTime timestamp to which addition has to take place.
Since:
  • 2.1.1
Returns:
Type
XsInteger

trim(str) → {XsString}

Return a string that removes leading empty spaces in the input string. Provides a client interface to a server function. See sql.trim
Parameters:
Name Type Argument Description
str XsString <optional>
The string to be evaluated.
Since:
  • 2.1.1
Returns:
Type
XsString

week(arg) → {XsInteger}

Returns an xs:integer between 1 and 53, both inclusive, representing the week value in the localized value of arg. Provides a client interface to a server function. See sql.week
Parameters:
Name Type Argument Description
arg Item <optional>
The dateTime/date/string whose day component will be returned.
Since:
  • 2.1.1
Returns:
Type
XsInteger

weekday(arg1) → {XsInteger}

Provides a client interface to a server function.
Parameters:
Name Type Argument Description
arg1 Item <optional>
-
Since:
  • 2.1.1
Returns:
Type
XsInteger

year(arg) → {XsInteger}

Returns an xs:integer representing the year component in the localized value of arg. The result may be negative. Provides a client interface to a server function. See sql.year
Parameters:
Name Type Argument Description
arg Item <optional>
The dateTime/date/string whose day component will be returned.
Since:
  • 2.1.1
Returns:
Type
XsInteger

yearday(arg) → {XsInteger}

Returns an xs:integer between 1 and 366, both inclusive, representing the yearday value in the localized value of arg. Provides a client interface to a server function. See sql.yearday
Parameters:
Name Type Argument Description
arg Item <optional>
The xs:genericDateTimeArg whose days of the year will be returned.
Since:
  • 2.1.1
Returns:
Type
XsInteger
Copyright 2014-2018 MarkLogic Corporation Documentation generated by JSDoc 3.5.5 on 2018-05-04T02:41:55-07:00 using the DocStrap template.