The utility module contains ora-fn functions for handling strings and dates. These functions are defined in XDK XQuery, whereas the oxh functions are specific to Oracle XQuery for Hadoop.
The utility functions are described in the following topics:
These functions are in the http://xmlns.oracle.com/xdk/xquery/function namespace. The ora-fn prefix is predeclared and the module is automatically imported.
The following functions are built in to Oracle XQuery for Hadoop:
Returns a new date value from a string according to the specified pattern.
ora-fn:date-from-string-with-format($format as xs:string?, $dateString as xs:string?, $locale as xs:string*) as xs:date? ora-fn:date-from-string-with-format($format as xs:string?, $dateString as xs:string?) as xs:date?
$format: The pattern; see "Format Argument"
$dateString: An input string that represents a date
$locale: A one- to three-field value that represents the locale; see "Locale Argument"
This example returns the specified date in the current time zone:
ora-fn:date-from-string-with-format("yyyy-MM-dd G", "2013-06-22 AD")
Returns a date string with the specified pattern.
ora-fn:date-to-string-with-format($format as xs:string?, $date as xs:date?, *$locale as xs:string?) as xs:string? ora-fn:date-to-string-with-format($format as xs:string?, $date as xs:date?) as xs:string?
$format: The pattern; see "Format Argument"
$date: The date
$locale: A one- to three-field value that represents the locale; see "Locale Argument"
This example returns the string 2013-07-15:
ora-fn:date-to-string-with-format("yyyy-mm-dd", xs:date("2013-07-15"))
Returns a new date-time value from an input string according to the specified pattern.
ora-fn:dateTime-from-string-with-format($format as xs:string?, $dateTimeString as xs:string?, $locale as xs:string?) as xs:dateTime? ora-fn:dateTime-from-string-with-format($format as xs:string?, $dateTimeString as xs:string?) as xs:dateTime?
$format: The pattern; see "Format Argument"
$dateTimeString: The date and time
$locale: A one- to three-field value that represents the locale; see "Locale Argument"
This example returns the specified date and 11:04:00AM in the current time zone:
ora-fn:dateTime-from-string-with-format("yyyy-MM-dd 'at' hh:mm", "2013-06-22 at 11:04")
The next example returns the specified date and 12:00:00AM in the current time zone:
ora-fn:dateTime-from-string-with-format("yyyy-MM-dd G", "2013-06-22 AD")
Returns a date and time string with the specified pattern.
ora-fn:dateTime-to-string-with-format($format as xs:string?, $dateTime as xs:dateTime?, $locale as xs:string?) as xs:string? ora-fn:dateTime-to-string-with-format($format as xs:string?, $dateTime as xs:dateTime?) as xs:string?
$format: The pattern; see "Format Argument"
$dateTime: The date and time
$locale: A one- to three-field value that represents the locale; see "Locale Argument"
This example returns the string 07 JAN 2013 10:09 PM AD:
ora-fn:dateTime-to-string-with-format("dd MMM yyyy hh:mm a G", xs:dateTime("2013-01-07T22:09:44"))
The next example returns the string "01-07-2013":
ora-fn:dateTime-to-string-with-format("MM-dd-yyyy", xs:dateTime("2013-01-07T22:09:44"))
Returns a new time value from an input string according to the specified pattern.
ora-fn:time-from-string-with-format($format as xs:string?, $timeString as xs:string?, $locale as xs:string?) as xs:time? ora-fn:time-from-string-with-format($format as xs:string?, $timeString as xs:string?) as xs:time?
$format: The pattern; see "Format Argument"
$timeString: The time
$locale: A one- to three-field value that represents the locale; see "Locale Argument"
This example returns 9:45:22 PM in the current time zone:
ora-fn:time-from-string-with-format("HH.mm.ss", "21.45.22")
The next example returns 8:07:22 PM in the current time zone:
fn-bea:time-from-string-with-format("hh:mm:ss a", "8:07:22 PM")
Returns a time string with the specified pattern.
ora-fn:time-to-string-with-format($format as xs:string?, $time as xs:time?, $locale as xs:string?) as xs:string? ora-fn:time-to-string-with-format($format as xs:string?, $time as xs:time?) as xs:string?
$format: The pattern; see "Format Argument"
$time: The time
$locale: A one- to three-field value that represents the locale; see "Locale Argument"
This example returns the string "10:09 PM":
ora-fn:time-to-string-with-format("hh:mm a", xs:time("22:09:44"))
The next example returns the string "22:09 PM":
ora-fn:time-to-string-with-format("HH:mm a", xs:time("22:09:44"))
The $format argument identifies the various fields that compose a date or time value.
See Also:
TheSimpleDateFormat class in the Java Standard Edition 7 Reference at
http://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html
The $locale represents a specific geographic, political, or cultural region defined by up to three fields:
Language code: The ISO 639 alpha-2 or alpha-3 language code, or the registered language subtags of up to eight letters. For example, en for English and ja for Japanese.
Country code: The ISO 3166 alpha-2 country code or the UN M.49 numeric-3 area code. For example, US for the United States and 029 for the Caribbean.
Variant: Indicates a variation of the locale, such as a particular dialect. Order multiple values in order of importance and separate them with an underscore (_). These values are case sensitive.
See Also:
The locale class in Java Standard Edition 7 Reference at
http://docs.oracle.com/javase/7/docs/api/java/util/Locale.html
All language, country, and variant codes in the Internet Assigned Numbers Authority (IANA) Language Subtag Registry at
http://www.iana.org/assignments/language-subtag-registry/language-subtag-registry
These functions are in the http://xmlns.oracle.com/xdk/xquery/function namespace. The ora-fn prefix is predeclared and the module is automatically imported.
The following functions are built in to Oracle XQuery for Hadoop:
Adds padding characters to the left of a string to create a fixed-length string. If the input string exceeds the specified size, then it is truncated to return a substring of the specified length.
The default padding character is a space (ASCII 32).
ora-fn:pad-left($str as xs:string?, $size as xs:integer?, $pad as xs:string?) as xs:string? ora-fn:pad-left($str as xs:string?, $size as xs:integer?) as xs:string?
$str: The input string
$size: The desired fixed length, which is obtained by adding padding characters to $str
$pad: The padding character
If either argument is an empty sequence, then the function returns an empty sequence.
This example prefixes "01" to the input string up to the maximum of six characters. The returned string is "010abc". The function returns one complete and one partial pad character.
ora-fn:pad-left("abc", 6, "01")
The example returns only "ab" because the input string exceeds the specified fixed length:
ora-fn:pad-left("abcd", 2, "01")
This example prefixes spaces to the string up to the specified maximum of six characters. The returned string has two spaces: " abcd":
ora-fn:pad-left("abcd", 6)
The next example returns only "ab" because the input string exceeds the specified fixed length:
ora-fn:pad-left("abcd", 2)
Adds padding characters to the right of a string to create a fixed-length string. If the input string exceeds the specified size, then it is truncated to return a substring of the specified length.
The default padding character is a space (ASCII 32).
ora-fn:pad-right($str as xs:string?, $size as xs:integer?, $pad as xs:string?) as xs:string? ora-fn:pad-right($str as xs:string?, $size as xs:integer?) as xs:string?
$str: The input string
$size: The desired fixed length, which is obtained by adding padding characters to $str
$pad: The padding character
If either argument is an empty sequence, then the function returns an empty sequence.
This example appends "01" to the input string up to the maximum of six characters. The returned string is "abc010". The function returns one complete and one partial pad character.
ora-fn:pad-right("abc", 6, "01")
This example returns only "ab" because the input string exceeds the specified fixed length:
ora-fn:pad-right("abcd", 2, "01")
This example appends spaces to the string up to the specified maximum of six characters. The returned string has two spaces: "abcd ":
ora-fn:pad-right("abcd", 6)
The next example returns only "ab" because the input string exceeds the specified fixed length:
ora-fn:pad-right("abcd", 2)
Removes any leading or trailing white space from a string.
ora-fn:trim($input as xs:string?) as xs:string?
$input: The string to trim. If $input is an empty sequence, then the function returns an empty sequence. Other data types trigger an error.
This example returns the string "abc":
ora-fn:trim(" abc ")
Removes any leading white space.
ora-fn:trim-left($input as xs:string?) as xs:string?
$input: The string to trim. If $input is an empty sequence, then the function returns an empty sequence. Other data types trigger an error.
This example removes the leading spaces and returns the string "abc ":
ora-fn:trim-left(" abc ")
Removes any trailing white space.
ora-fn:trim-right($input as xs:string?) as xs:string?
$input: The string to trim. If $input is an empty sequence, then the function returns an empty sequence. Other data types trigger an error.
This example removes the trailing spaces and returns the string " abc":
ora-fn:trim-left(" abc ")