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

bucket(bucketEdgesParam, srchParam, collationLiteral) → {XsUnsignedLong}

Returns an unsignedLong specifying the index of the bucket the second parameter belongs to in buckets formed by the first parameter. Values that lie on the edge of a bucket fall to the greater index. Provides a client interface to a server function. See sql.bucket

Parameters:

Name	Type	Argument	Description
`bucketEdgesParam`	XsAnyAtomicType	<optional>	A sequence of ordered values indicating the edges of a collection of buckets. If the sequence is out of order or has duplicates, SQL-UNORDERED is thrown.
`srchParam`	XsAnyAtomicType	<optional>	A value to find an index for in the bucket edge list.
`collationLiteral`	XsString	<optional>	A collation identifier. All bucketEdgesParam and srcParam are converted to a string of this collation if supplied.

Since:

2.1.1

Returns:

Type: XsUnsignedLong

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

glob(input, pattern) → {XsBoolean}

Returns true if the specified input glob the specified pattern, otherwise returns false. Provides a client interface to a server function. See sql.glob

Parameters:

Name	Type	Argument	Description
`input`	XsString	<optional>	The input from which to match.
`pattern`	XsString	<optional>	The expression to match. '?' matches one character and '*' matches any number of characters.

Since:

2.1.1

Returns:

Type: XsBoolean

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

ifnull(expr1, expr2) → {XsAnyAtomicType}

If the first expression is NULL, then the value of the second expression is returned. If not null, the first expression is returned. Provides a client interface to a server function. See sql.ifnull

Parameters:

Name	Type	Argument	Description
`expr1`	Item	<optional>	First expression to be evaluated.
`expr2`	Item	<optional>	Second expression to be evaluated.

Since:

2.1.1

Returns:

Type: XsAnyAtomicType

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`	XsAnyAtomicType	<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

like(input, pattern, escape) → {XsBoolean}

Returns true if the specified input like the specified pattern, otherwise returns false. Provides a client interface to a server function. See sql.like

Parameters:

Name	Type	Argument	Description
`input`	XsString	<optional>	The input from which to match.
`pattern`	XsString	<optional>	The expression to match. '_' matches one character and '%' matches any number of characters.
`escape`	XsString	<optional>	If a '_' or '%' are preceeded by an escape character then it will be match as the char '_'/'%' themselves.

Since:

2.1.1

Returns:

Type: XsBoolean

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

nullif(expr1, expr2) → {XsAnyAtomicType}

Returns a NULL value if the two specified values are equal. Returns the first value if they are not equal Provides a client interface to a server function. See sql.nullif

Parameters:

Name	Type	Argument	Description
`expr1`	Item	<optional>	First expression to be evaluated.
`expr2`	Item	<optional>	Second expression to be evaluated.

Since:

2.1.1

Returns:

Type: XsAnyAtomicType

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

right(str, n) → {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`	XsAnyAtomicType	<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

rowID(arg1) → {SqlRowID}

Constructs a row identifier from the string form of the temporary identifier assigned to a row during processing. Provides a client interface to a server function.

Parameters:

Name	Type	Argument	Description
`arg1`	XsAnyAtomicType	<optional>	-

Since:

2.1.1

Returns:

Type: SqlRowID

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) → {XsNumeric}

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: XsNumeric

soundex(arg) → {XsString}

Returns a four-character (SOUNDEX) code to evaluate the similarity of two strings. Provides a client interface to a server function. See sql.soundex

Parameters:

Name	Type	Argument	Description
`arg`	XsString	<optional>	The string whose soundex will be returned.

Since:

2.1.1

Returns:

Type: XsString

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

strpos(target, test, collation) → {XsInteger}

Returns an integer value representing the starting position of a string within the search string. Note, the string starting position is 1. If the first parameter is empty, the result is the empty sequence. Provides a client interface to a server function. See sql.strpos

Parameters:

Name	Type	Argument	Description
`target`	XsString	<optional>	The string from which to test.
`test`	XsString	<optional>	The string to test for existence in the second parameter.
`collation`	XsString	<optional>	The optional name of a valid collation URI. For information on the collation URI syntax, see the Search Developer's Guide.

Since:

2.1.1

Returns:

Type: XsInteger

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 and trailing 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}

Returns the day of the week. 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