2
: Copyright 2006-2009 The FLWOR Foundation.
4
: Licensed under the Apache License, Version 2.0 (the "License");
5
: you may not use this file except in compliance with the License.
6
: You may obtain a copy of the License at
8
: http://www.apache.org/licenses/LICENSE-2.0
10
: Unless required by applicable law or agreed to in writing, software
11
: distributed under the License is distributed on an "AS IS" BASIS,
12
: WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13
: See the License for the specific language governing permissions and
14
: limitations under the License.
18
: This is a library module offering the same set of functions
19
: defined by Microsoft Excel, under Text and Data Functions.
23
: @see <a href="http://office.microsoft.com/en-us/excel/CH062528321033.aspx"
24
: target="_blank">Excel Documentation: Text Functions</a>
27
module namespace excel-text = "http://www.zorba-xquery.com/modules/excel/text" ;
30
: Use excel-err module functions for throwing errors.
32
import module namespace excel-err="http://www.zorba-xquery.com/modules/excel/errors";
35
: Import excel-math module functions.
37
import module namespace excel-math="http://www.zorba-xquery.com/modules/excel/math";
40
: Returns the union of the values in two sequences in an implementation-defined order. It removes duplicates.
42
: @param $arg1 the first sequence.
43
: @param $arg2 the second sequence.
44
: @return The union of the values in two sequences in an implementation-defined order. It removes duplicates.
46
declare %private function excel-text:value-union
47
( $arg1 as xs:anyAtomicType* ,
48
$arg2 as xs:anyAtomicType* ) as xs:anyAtomicType* {
50
fn:distinct-values(($arg1, $arg2))
54
: Returns the intersection of the values in two sequences in an implementation-defined order. It removes duplicates.
56
: @param $arg1 the first sequence.
57
: @param $arg2 the second sequence.
58
: @return The intersection of the values in two sequences in an implementation-defined order. It removes duplicates.
60
declare %private function excel-text:value-intersect
61
( $arg1 as xs:anyAtomicType* ,
62
$arg2 as xs:anyAtomicType* ) as xs:anyAtomicType* {
64
fn:distinct-values($arg1[.=$arg2])
68
: Returns the values in one sequence that do not appear in the second sequence in an implementation-defined order.
70
: @param $arg1 the first sequence.
71
: @param $arg2 the second sequence.
72
: @return The values in one sequence that do not appear in the second sequence in an implementation-defined order.
74
declare function excel-text:value-except
75
( $arg1 as xs:anyAtomicType* ,
76
$arg2 as xs:anyAtomicType* ) as xs:anyAtomicType* {
78
fn:distinct-values($arg1[not(.=$arg2)])
82
: Reverses the order of characters in a string.
84
: @param $arg the string.
85
: @return Reverses the order of characters in a string or zero-length string if the argument is the empty sequence.
87
declare %private function excel-text:reverse-string
88
( $arg as xs:string? ) as xs:string {
90
fn:codepoints-to-string(fn:reverse(fn:string-to-codepoints($arg)))
94
: Returns $string appended with enough repetitions of $padChar to make its length $length.
96
: @param $string the string.
97
: @param $padChar the character used for padding.
98
: @param $length the desired length.
99
: @return $string appended with enough repetitions of $padChar to make its length $length.<br/>
100
: The $string is trunctated if it's length is greater than $length.
102
declare %private function excel-text:pad-string-to-length
103
( $string as xs:string? ,
104
$padChar as xs:string ,
105
$length as xs:integer ) as xs:string {
107
fn:substring(fn:string-join (($string, for $i in (1 to $length) return $padChar),'')
112
: Returns $toPad appended with enough repetitions of $padChar to make its length $length, the characters are added before the string.
114
: @param $toPad the value to be padded.
115
: @param $padChar the character used for padding.
116
: @param $length the desired length.
117
: @error XQP0021(errValue) if the length of the $toPad is greater than the desired length.
118
: @return $toPad appended with enough repetitions of $padChar to make its length $length, the characters are added before the string.
120
declare function excel-text:pad-integer-to-length
121
( $toPad as xs:anyAtomicType?,
122
$padChar as xs:string ,
123
$length as xs:integer) as xs:string {
125
if ($length < fn:string-length(fn:string($toPad)))
126
then fn:error($excel-err:errValue, "Number can not ne padded to the desired length", $length)
128
excel-text:reverse-string(excel-text:pad-string-to-length(
129
excel-text:reverse-string(fn:string($toPad)),fn:substring($padChar, 1, 1),$length))
133
: Returns an integer representing the first position of a substring that matches $pattern within $arg.
135
: @param $arg the string.
136
: @param $pattern the pattern to match.
137
: @return An integer representing the first position of a substring that matches $pattern within $arg.<br />
138
: If $arg does not match $pattern, the empty sequence is returned.
140
declare %private function excel-text:index-of-match-first
141
( $arg as xs:string? ,
142
$pattern as xs:string ) as xs:integer? {
144
if (fn:matches($arg,$pattern))
145
then fn:string-length(tokenize($arg, $pattern)[1]) + 1
150
: Returns an integer representing the first position of a substring that matches $pattern using $flags within $arg.
152
: @param $arg the string.
153
: @param $pattern the pattern to match.
154
: @param $flags options for the interpretation of the regular expression.
155
: @return An integer representing the first position of a substring that matches $pattern using $flags within $arg.<br />
156
: If $arg does not match $pattern, the empty sequence is returned.
158
declare %private function excel-text:index-of-match-first
159
( $arg as xs:string? ,
160
$pattern as xs:string,
161
$flags as xs:string ) as xs:integer? {
163
if (matches($arg,$pattern,$flags))
164
then fn:string-length(tokenize($arg, $pattern, $flags)[1]) + 1
169
: Returns an integer representing the $instance_num position of a substring that matches $pattern within $arg starting from $pos.
171
: @param $arg the string.
172
: @param $pattern the pattern to match.
173
: @param $pos the position to start the search.
174
: @param $instance_num the instance match number.
175
: @return An integer representing the $instance_num position of a substring that matches $pattern within $arg starting from $pos. <br />
176
: If $arg does not match $pattern, the empty sequence is returned.
178
declare %private function excel-text:index-of-match
179
( $arg as xs:string? ,
180
$pattern as xs:string,
182
$instance_num as xs:integer) as xs:integer? {
184
if($instance_num eq 1)
185
then $pos + excel-text:index-of-match-first(fn:substring($arg,$pos),$pattern) - 1
187
let $index := excel-text:index-of-match-first(fn:substring($arg,$pos),$pattern)
188
return if(fn:not(fn:exists($index)))
190
else excel-text:index-of-match($arg, $pattern, $pos + $index, ($instance_num - 1))
194
: Splits $text in groups of $length characters starting from right to left.
196
: @param $text the string.
197
: @param $length the length of the group.
198
: @return Splits $text in groups of $length characters starting from right to left.
200
declare %private function excel-text:tokenize-length
202
$length as xs:decimal) as xs:string* {
204
if(fn:string-length($text) <= $length) then
207
let $groupsNo := fn:string-length($text) idiv $length
208
let $tmp := fn:string-length($text) mod $length
211
for $i in (1 to $groupsNo)
212
return fn:substring($text, (1 + ($length*($i - 1))), $length)
214
for $i in (0 to $groupsNo)
217
fn:substring($text, 1, $tmp)
219
fn:substring($text, $tmp + ($length*($i - 1)) + 1, $length)
223
: Returns the given $text unchanged.
225
: @see http://office.microsoft.com/en-us/excel/HP052508361033.aspx
226
: @param $text the time
227
: @return The given $text unchanged.
229
declare function excel-text:asc
230
( $text as xs:string) as xs:string {
236
: Returns the character specified by a certain codepoint.
238
: @see http://office.microsoft.com/en-us/excel/HP052090091033.aspx
239
: @param $number the codepoint.
240
: @error XQP0021(errValue) provided $number must be in range [1,255].
241
: @return the character specified by a certain codepoint.
242
Zorba uses UTF-8 encoding so the actual codepoint range is between [1,1114111]
244
declare function excel-text:char
245
( $number as xs:integer) as xs:string {
247
if( (1 <= $number) and ($number <= 255)) then
248
fn:codepoints-to-string($number)
250
fn:error($excel-err:errValue, "Provided number must be in range [1,255]", $number)
254
: Returns a codepoint for the first character in a text string.
256
: @see http://office.microsoft.com/en-us/excel/HP052090151033.aspx
257
: @param $arg the string.
258
: @error XQP0021(errValue) Provided $arg was empty.
259
: @return A codepoint for the first character in a text string.
261
declare function excel-text:code
262
( $arg as xs:string) as xs:integer {
264
if( fn:string-length($arg) > 0 ) then
265
fn:string-to-codepoints(fn:substring($arg, 1, 1))
267
fn:error($excel-err:errValue, "Provided text was empty",$arg)
271
: Joins two text strings into one text string.
273
: @see http://office.microsoft.com/en-us/excel/HP052090151033.aspx
274
: @param $arg1 the first string.
275
: @param $arg2 the second string.
276
: @return Joins two text strings into one text string.
278
declare function excel-text:concatenate
279
( $arg1 as xs:anyAtomicType?,
280
$arg2 as xs:anyAtomicType?) as xs:string {
282
fn:concat($arg1,$arg2)
286
: Joins several text strings into one text string.
288
: @see http://office.microsoft.com/en-us/excel/HP052090151033.aspx
289
: @param $args a sequence of strings.
290
: @return Joins several text strings into one text string.
292
declare function excel-text:concatenate
293
( $args as xs:anyAtomicType*) as xs:string {
295
fn:string-join((for $value in $args return xs:string($value)), "")
299
: Removes all nonprintable characters from text.
301
: @see http://office.microsoft.com/en-us/excel/HP052090141033.aspx
302
: @param $arg the string.
303
: @return Removes all nonprintable characters from text. The CLEAN function was designed.
304
: to remove the first 32 nonprinting characters in the 7-bit ASCII code (values 0 through 31) from text. <br/>
305
: In the Unicode character set, there are additional nonprinting characters (values 127, 129, 141, 143, 144, and 157). <br/>
306
: By itself, the CLEAN function does not remove these additional nonprinting characters.
308
declare function excel-text:clean
309
( $arg as xs:string? ) as xs:string? {
311
fn:codepoints-to-string( for $codepoint in fn:string-to-codepoints($arg)
312
return if ($codepoint < 32) then () else $codepoint)
316
: Rounds a number to the specified number of decimals, formats the number in
317
: decimal format using a period and commas, and returns the result as text.
319
: @see http://office.microsoft.com/en-us/excel/HP052090931033.aspx
320
: @param $number is the number you want to round and convert to text.
321
: @param $decimals is the number of digits to the right of the decimal point.
322
: @return Rounds a number to the specified number of decimals, formats the number in
323
: decimal format using a period and commas, and returns the result as text.
325
declare function excel-text:fixed
326
( $number as xs:decimal,
327
$decimals as xs:decimal) as xs:string {
329
excel-text:fixed($number,$decimals,fn:false())
333
: Rounds a number to the specified number of decimals, formats the number in
334
:decimal format using a period and commas, and returns the result as text.
336
: @see http://office.microsoft.com/en-us/excel/HP052090931033.aspx
337
: @param $number is the number you want to round and convert to text.
338
: @param $decimals is the number of digits to the right of the decimal point.
339
: @param $no_commas is a logical value that, if TRUE, prevents FIXED from
340
:including commas in the returned text.
341
: @return Rounds a number to the specified number of decimals, formats the number in
342
:decimal format using a period and commas, and returns the result as text.
344
declare function excel-text:fixed
345
( $number as xs:decimal,
346
$decimals as xs:decimal,
347
$no_commas as xs:boolean) as xs:string {
349
let $tmp := excel-math:round($number, $decimals)
351
return if($no_commas) then
354
if(fn:contains(xs:string($tmp), '.')) then
355
let $x := fn:tokenize(xs:string($tmp),'[.]')[1]
356
let $y := fn:tokenize(xs:string($tmp),'[.]')[2]
357
let $x := fn:string-join(excel-text:tokenize-length($x,3), ',')
358
return fn:string-join(($x,$y),'.')
360
fn:string-join(excel-text:tokenize-length(fn:string($tmp),3), ',')
364
: Converts a number to text format and applies a currency symbol. The number of
365
:digits to the right of the decimal point is 2.
367
: @see http://office.microsoft.com/en-us/excel/HP052090931033.aspx
368
: @param $number is the number.
369
: @return Converts a number to text format and applies a currency symbol. The number of
370
:digits to the right of the decimal point is 2.
372
declare function excel-text:dollar
373
( $number as xs:decimal) as xs:string {
375
excel-text:dollar($number, 2)
379
: Converts a number to text format and applies a currency symbol.
381
: @see http://office.microsoft.com/en-us/excel/HP052090931033.aspx
382
: @param $number is the number.
383
: @param $decimals is the number of digits to the right of the decimal point. <br/>
384
:If decimals is negative, number is rounded to the left of the decimal point.
385
: @return Converts a number to text format and applies a currency symbol.
387
declare function excel-text:dollar
388
( $number as xs:decimal,
389
$decimals as xs:decimal) as xs:string {
391
let $int := fn:tokenize(fn:concat("$", excel-text:fixed(excel-math:abs($number), $decimals)),'\.')[1]
392
let $decimal := fn:tokenize(fn:concat("$", excel-text:fixed(excel-math:abs($number), $decimals)),'\.')[2]
394
return if( fn:empty($decimal) ) then $int
395
else fn:concat($int,'.',excel-text:pad-string-to-length($decimal,"0",$decimals))
399
: Compares two text strings and returns TRUE if they are exactly the same,
400
:FALSE otherwise. EXACT is case-sensitive but ignores formatting differences.
402
: @see http://office.microsoft.com/en-us/excel/HP052090811033.aspx
403
: @param $arg1 the first string.
404
: @param $arg2 the second string.
405
: @return Compares two text strings and returns TRUE if they are exactly the same,
406
:FALSE otherwise. EXACT is case-sensitive but ignores formatting differences.
408
declare function excel-text:exact
410
$arg2 as xs:string) as xs:boolean {
416
: Returns the first character in a text string.
418
: @see http://office.microsoft.com/en-us/excel/HP052091531033.aspx
419
: @param $arg is the text string that contains the characters you want to extract.
420
: @return The first character in a text string.
422
declare function excel-text:left
423
( $arg as xs:string) as xs:string {
424
excel-text:left($arg, 1)
428
: Returns the first character or characters in $text, based on the number of $num_chars you specify.
430
: @see http://office.microsoft.com/en-us/excel/HP052091531033.aspx
431
: @param $text is the text string that contains the characters you want to extract.
432
: @param $num_chars specifies the number of characters you want to extract.
433
: @return The first character or characters in $text, based on the number of $num_chars you specify.
435
declare function excel-text:left
436
( $text as xs:string,
437
$num_chars as xs:integer) as xs:string {
439
let $chars := if (fn:string-length($text) < $num_chars) then
440
fn:string-length($text)
444
return fn:substring($text, 1, $chars)
448
: Returns the number of characters in a text string.
450
: @see http://office.microsoft.com/en-us/excel/HP052091541033.aspx
451
: @param $arg the string.
452
: @return The number of characters in a text string.
454
declare function excel-text:len
455
( $arg as xs:string?) as xs:integer {
457
fn:string-length($arg)
461
: Converts all uppercase letters in a text string to lowercase.
463
: @see http://office.microsoft.com/en-us/excel/HP052091671033.aspx
464
: @param $arg the string.
465
: @return Converts all uppercase letters in a text string to lowercase.
467
declare function excel-text:lower
468
( $arg as xs:string?) as xs:string? {
474
: Returns a specific number of characters from a text string, starting at
475
:the position you specify, based on the number of characters you specify.
477
: @see http://office.microsoft.com/en-us/excel/HP052091751033.aspx
478
: @param $text the text string containing the characters you want to extract.
479
: @param $start_num the position of the first character you want to extract
480
: in text. The first character in text has start_num 1, and so on.
481
: @param $num_chars the number of characters you want to return from text.
482
: @return A specific number of characters from a text string, starting at
483
: the position you specify, based on the number of characters you specify.
485
declare function excel-text:mid
486
( $text as xs:string?,
487
$start_num as xs:integer,
488
$num_chars as xs:integer) as xs:string? {
489
let $len := fn:string-length($text)
491
if ($start_num > $len) then
493
else if ($start_num < 1) then
494
fn:error($excel-err:errValue, "Provided value is less than 1", $start_num)
495
else if ($num_chars < 0) then
496
fn:error($excel-err:errValue, "Provided value is less than zero", $num_chars)
498
fn:substring($text, $start_num, $num_chars)
502
: Replaces part of a text string, based on the number of characters you specify, with a different text string.
504
: @see http://office.microsoft.com/en-us/excel/HP052092351033.aspx
505
: @param $old_text is text in which you want to replace some characters.
506
: @param $start_num the position of the character in old_text that you want to replace with new_text.
507
: @param $num_chars the number of characters in old_text that you want REPLACE to replace with new_text.
508
: @param $new_text the text that will replace characters in old_text.
509
: @return Replaces part of a text string, based on the number of characters you specify, with a different text string.
511
declare function excel-text:replace
512
( $old_text as xs:string?,
513
$start_num as xs:integer,
514
$num_chars as xs:integer,
515
$new_text as xs:string) as xs:string {
517
fn:concat(fn:substring($old_text,0,$start_num),
519
fn:substring($old_text,$start_num + $num_chars))
523
: Returns the last character in a text string.
525
: @see http://office.microsoft.com/en-us/excel/HP052092371033.aspx
526
: @param $arg the text string containing the characters you want to extract.
527
: @return The last character in a text string.
529
declare function excel-text:right
530
( $arg as xs:string) as xs:string {
532
excel-text:right($arg, 1)
536
: Returns the last character or characters in a text string, based on the number of characters you specify.
538
: @see http://office.microsoft.com/en-us/excel/HP052092371033.aspx
539
: @param $text the text string containing the characters you want to extract.
540
: @param $num_chars specifies the number of characters you want RIGHT to extract.
541
: @return The last character or characters in a text string, based on the number of characters you specify.
543
declare function excel-text:right
544
( $text as xs:string,
545
$num_chars as xs:integer) as xs:string {
547
let $chars := if (fn:string-length($text) < $num_chars) then fn:string-length($text) else $num_chars
548
return fn:substring($text, (fn:string-length($text) - $chars + 1), $chars)
552
: Locate one text string within a second text string, and return the number of
553
: the starting position of the first text string from the first character of the
554
: second text string. <br/> The search starts at position 1, and it is not case sensitive.
556
: @see http://office.microsoft.com/en-us/excel/HP052092491033.aspx
557
: @param $find_text text you want to find.
558
: @param $within_text text in which you want to search for $find_text.
559
: @error XQP0021(errValue) the value is not greater than zero or is greater than the length of within_text.
560
: @error XQP0021(errValue) value was not found.
561
: @return Locate one text string within a second text string, and return the number of
562
: the starting position of the first text string from the first character of the
563
: second text string. <br/> The search starts at position 1, and it is not case sensitive.
565
declare function excel-text:search
566
( $find_text as xs:string,
567
$within_text as xs:string) as xs:integer? {
569
excel-text:search($find_text, $within_text, 1)
573
: Locate one text string within a second text string, and return the number of
574
: the starting position of the first text string from the first character of the
575
: second text string.<br/> The search starts at $start_num, and it is not case sensitive.
577
: @see http://office.microsoft.com/en-us/excel/HP052092491033.aspx
578
: @param $find_text text you want to find.
579
: @param $within_text text in which you want to search for $find_text.
580
: @param $start_num the character number in within_text at which you want to start searching.
581
: @error XQP0021(errValue) the value is not greater than zero or is greater than the length of within_text.
582
: @error XQP0021(errValue) value was not found.
583
: @return Locate one text string within a second text string, and return the number of
584
: the starting position of the first text string from the first character of the
585
: second text string.<br/> The search starts at $start_num, and it is not case sensitive.
587
declare function excel-text:search
588
( $find_text as xs:string,
589
$within_text as xs:string,
590
$start_num as xs:integer) as xs:integer? {
592
if(($start_num < 1) or ($start_num > fn:string-length($within_text))) then
593
fn:error($excel-err:errValue, "The value is not greater than zero or is greater than the length of within_text", $start_num)
595
let $source := fn:substring($within_text, $start_num)
597
return if(fn:exists(excel-text:index-of-match-first($source, $find_text, "i"))) then
598
$start_num + excel-text:index-of-match-first($source, $find_text, "i") -1
600
fn:error($excel-err:errValue, "Value was not found", $find_text)
604
: Locate one text string within a second text string, and return the number of the
605
: starting position of the first text string from the first character of the second text string. <br/>
606
: The search is case sensitive.
608
: @see http://office.microsoft.com/en-us/excel/HP052090891033.aspx
609
: @param $find_text text you want to find.
610
: @param $within_text text in which you want to search for $find_text.
611
: @error XQP0021(errValue) the value is not greater than zero or is greater than the length of within_text.
612
: @error XQP0021(errValue) value was not found.
613
: @return Locate one text string within a second text string, and return the number of the
614
: starting position of the first text string from the first character of the second text string. <br/>
615
: The search is case sensitive.
617
declare function excel-text:find
618
( $find_text as xs:string,
619
$within_text as xs:string) as xs:integer? {
621
excel-text:find($find_text, $within_text, 1)
625
: Locate one text string within a second text string, and return the number of the
626
: starting position of the first text string from the first character of the second text string.<br/>
627
: The search is case sensitive.
629
: @see http://office.microsoft.com/en-us/excel/HP052090891033.aspx
630
: @param $find_text text you want to find.
631
: @param $within_text text in which you want to search for $find_text.
632
: @param $start_num specifies the character at which to start the search.
633
: @error XQP0021(errValue) the value is not greater than zero or is greater than the length of within_text.
634
: @error XQP0021(errValue) value was not found.
635
: @return Locate one text string within a second text string, and return the number of the
636
: starting position of the first text string from the first character of the second text string.<br/>
637
: The search is case sensitive.
639
declare function excel-text:find
640
( $find_text as xs:string,
641
$within_text as xs:string,
642
$start_num as xs:integer) as xs:integer? {
644
if(($start_num < 1) or ($start_num > fn:string-length($within_text))) then
645
fn:error($excel-err:errValue, "The value is not greater than zero or is greater than the length of within_text", $start_num)
647
let $source := fn:substring($within_text, $start_num)
649
return if(fn:exists(excel-text:index-of-match-first($source, $find_text))) then
650
$start_num + excel-text:index-of-match-first($source, $find_text) -1
652
fn:error($excel-err:errValue, "Value was not found", $find_text)
656
: Substitutes new_text for old_text in a text string.
658
: @see http://office.microsoft.com/en-us/excel/HP052092861033.aspx
659
: @param $text the text or the reference to a cell containing text for which you want to substitute characters.
660
: @param $old_text text you want to replace.
661
: @param $new_text text you want to replace old_text with.
662
: @param $instance_num specifies which occurrence of old_text you want to replace with new_text. <br/>
663
: Only that instance of old_text is replaced.
664
: @return Substitutes new_text for old_text in a text string. <br/> Use SUBSTITUTE when you
665
: want to replace specific text in a text string; use REPLACE when you want
666
: to replace any text that occurs in a specific location in a text string.
668
declare function excel-text:substitute
669
( $text as xs:string,
670
$old_text as xs:string,
671
$new_text as xs:string,
672
$instance_num as xs:integer?) as xs:string {
674
let $startPos := excel-text:index-of-match($text,$old_text,1,$instance_num)
676
if(fn:empty($startPos)) then $text
677
else excel-text:replace($text, $startPos, fn:string-length($old_text), $new_text)
681
: Substitutes new_text for old_text in a text string. Every occurrence of old_text in text is changed to new_text.
683
: @see http://office.microsoft.com/en-us/excel/HP052092861033.aspx
684
: @param $text the text or the reference to a cell containing text for which you want to substitute characters.
685
: @param $old_text text you want to replace.
686
: @param $new_text text you want to replace old_text with.
687
: @return Substitutes new_text for old_text in a text string. Every occurrence of old_text in text is changed to new_text.
689
declare function excel-text:substitute
690
( $text as xs:string,
691
$old_text as xs:string,
692
$new_text as xs:string) as xs:string? {
694
fn:replace($text, $old_text, $new_text)
698
: Removes all spaces from text except for single spaces between words.
700
: @see http://office.microsoft.com/en-us/excel/HP052093211033.aspx?pid=CH062528321033
701
: @param $text from which you want spaces removed.
702
: @return Removes all spaces from text except for single spaces between words.
704
declare function excel-text:trim
705
( $text as xs:string?) as xs:string? {
707
fn:normalize-space($text)
711
: Converts text to uppercase.
713
: @see http://office.microsoft.com/en-us/excel/HP052093271033.aspx
714
: @param $text text you want converted to uppercase.
715
: @return Converts text to uppercase.
717
declare function excel-text:upper
718
( $text as xs:string?) as xs:string? {
724
: Converts the $value to string.
726
: @see http://office.microsoft.com/en-us/excel/HP052093041033.aspx
727
: @param $value the value
728
: @return Converts the $value to string.
730
declare function excel-text:t
731
( $value as xs:anyAtomicType?) as xs:string {
733
if( $value instance of xs:string ) then fn:string($value)
738
: Converts a text string that represents a number to a number.
740
: @see http://office.microsoft.com/en-us/excel/HP052093291033.aspx
741
: @param $arg the value.
742
: @error XQP0021(errValue) provided value is not a number.
743
: @return Converts a text string that represents a number to a number.
745
declare function excel-text:value
746
( $arg as xs:anyAtomicType?) as xs:anyAtomicType? {
749
case xs:double return $arg
750
case xs:decimal return $arg
751
case xs:double return $arg
752
case xs:float return $arg
754
if($arg castable as xs:integer) then
757
if($arg castable as xs:decimal) then
760
if($arg castable as xs:double) then
763
fn:error($excel-err:errValue, "Provided value is not a number", $arg)
2
: Copyright 2006-2009 The FLWOR Foundation.
4
: Licensed under the Apache License, Version 2.0 (the "License");
5
: you may not use this file except in compliance with the License.
6
: You may obtain a copy of the License at
8
: http://www.apache.org/licenses/LICENSE-2.0
10
: Unless required by applicable law or agreed to in writing, software
11
: distributed under the License is distributed on an "AS IS" BASIS,
12
: WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13
: See the License for the specific language governing permissions and
14
: limitations under the License.
18
: This is a library module offering the same set of functions
19
: defined by Microsoft Excel, under Text and Data Functions.
23
: @see <a href="http://office.microsoft.com/en-us/excel/CH062528321033.aspx"
24
: target="_blank">Excel Documentation: Text Functions</a>
27
module namespace excel-text = "http://www.zorba-xquery.com/modules/excel/text" ;
30
: Use excel-err module functions for throwing errors.
32
import module namespace excel-err="http://www.zorba-xquery.com/modules/excel/errors";
35
: Import excel-math module functions.
37
import module namespace excel-math="http://www.zorba-xquery.com/modules/excel/math";
40
: Returns the union of the values in two sequences in an implementation-defined order. It removes duplicates.
42
: @param $arg1 the first sequence.
43
: @param $arg2 the second sequence.
44
: @return The union of the values in two sequences in an implementation-defined order. It removes duplicates.
46
declare %private function excel-text:value-union
47
( $arg1 as xs:anyAtomicType* ,
48
$arg2 as xs:anyAtomicType* ) as xs:anyAtomicType* {
50
fn:distinct-values(($arg1, $arg2))
54
: Returns the intersection of the values in two sequences in an implementation-defined order. It removes duplicates.
56
: @param $arg1 the first sequence.
57
: @param $arg2 the second sequence.
58
: @return The intersection of the values in two sequences in an implementation-defined order. It removes duplicates.
60
declare %private function excel-text:value-intersect
61
( $arg1 as xs:anyAtomicType* ,
62
$arg2 as xs:anyAtomicType* ) as xs:anyAtomicType* {
64
fn:distinct-values($arg1[.=$arg2])
68
: Returns the values in one sequence that do not appear in the second sequence in an implementation-defined order.
70
: @param $arg1 the first sequence.
71
: @param $arg2 the second sequence.
72
: @return The values in one sequence that do not appear in the second sequence in an implementation-defined order.
74
declare function excel-text:value-except
75
( $arg1 as xs:anyAtomicType* ,
76
$arg2 as xs:anyAtomicType* ) as xs:anyAtomicType* {
78
fn:distinct-values($arg1[not(.=$arg2)])
82
: Reverses the order of characters in a string.
84
: @param $arg the string.
85
: @return Reverses the order of characters in a string or zero-length string if the argument is the empty sequence.
87
declare %private function excel-text:reverse-string
88
( $arg as xs:string? ) as xs:string {
90
fn:codepoints-to-string(fn:reverse(fn:string-to-codepoints($arg)))
94
: Returns $string appended with enough repetitions of $padChar to make its length $length.
96
: @param $string the string.
97
: @param $padChar the character used for padding.
98
: @param $length the desired length.
99
: @return $string appended with enough repetitions of $padChar to make its length $length.<br/>
100
: The $string is trunctated if it's length is greater than $length.
102
declare %private function excel-text:pad-string-to-length
103
( $string as xs:string? ,
104
$padChar as xs:string ,
105
$length as xs:integer ) as xs:string {
107
fn:substring(fn:string-join (($string, for $i in (1 to $length) return $padChar),'')
112
: Returns $toPad appended with enough repetitions of $padChar to make its length $length, the characters are added before the string.
114
: @param $toPad the value to be padded.
115
: @param $padChar the character used for padding.
116
: @param $length the desired length.
117
: @error XQP0021(errValue) if the length of the $toPad is greater than the desired length.
118
: @return $toPad appended with enough repetitions of $padChar to make its length $length, the characters are added before the string.
120
declare function excel-text:pad-integer-to-length
121
( $toPad as xs:anyAtomicType?,
122
$padChar as xs:string ,
123
$length as xs:integer) as xs:string {
125
if ($length < fn:string-length(fn:string($toPad)))
126
then fn:error($excel-err:errValue, "Number can not ne padded to the desired length", $length)
128
excel-text:reverse-string(excel-text:pad-string-to-length(
129
excel-text:reverse-string(fn:string($toPad)),fn:substring($padChar, 1, 1),$length))
133
: Returns an integer representing the first position of a substring that matches $pattern within $arg.
135
: @param $arg the string.
136
: @param $pattern the pattern to match.
137
: @return An integer representing the first position of a substring that matches $pattern within $arg.<br />
138
: If $arg does not match $pattern, the empty sequence is returned.
140
declare %private function excel-text:index-of-match-first
141
( $arg as xs:string? ,
142
$pattern as xs:string ) as xs:integer? {
144
if (fn:matches($arg,$pattern))
145
then fn:string-length(tokenize($arg, $pattern)[1]) + 1
150
: Returns an integer representing the first position of a substring that matches $pattern using $flags within $arg.
152
: @param $arg the string.
153
: @param $pattern the pattern to match.
154
: @param $flags options for the interpretation of the regular expression.
155
: @return An integer representing the first position of a substring that matches $pattern using $flags within $arg.<br />
156
: If $arg does not match $pattern, the empty sequence is returned.
158
declare %private function excel-text:index-of-match-first
159
( $arg as xs:string? ,
160
$pattern as xs:string,
161
$flags as xs:string ) as xs:integer? {
163
if (matches($arg,$pattern,$flags))
164
then fn:string-length(tokenize($arg, $pattern, $flags)[1]) + 1
169
: Returns an integer representing the $instance_num position of a substring that matches $pattern within $arg starting from $pos.
171
: @param $arg the string.
172
: @param $pattern the pattern to match.
173
: @param $pos the position to start the search.
174
: @param $instance_num the instance match number.
175
: @return An integer representing the $instance_num position of a substring that matches $pattern within $arg starting from $pos. <br />
176
: If $arg does not match $pattern, the empty sequence is returned.
178
declare %private function excel-text:index-of-match
179
( $arg as xs:string? ,
180
$pattern as xs:string,
182
$instance_num as xs:integer) as xs:integer? {
184
if($instance_num eq 1)
185
then $pos + excel-text:index-of-match-first(fn:substring($arg,$pos),$pattern) - 1
187
let $index := excel-text:index-of-match-first(fn:substring($arg,$pos),$pattern)
188
return if(fn:not(fn:exists($index)))
190
else excel-text:index-of-match($arg, $pattern, $pos + $index, ($instance_num - 1))
194
: Splits $text in groups of $length characters starting from right to left.
196
: @param $text the string.
197
: @param $length the length of the group.
198
: @return Splits $text in groups of $length characters starting from right to left.
200
declare %private function excel-text:tokenize-length
202
$length as xs:decimal) as xs:string* {
204
if(fn:string-length($text) <= $length) then
207
let $groupsNo := fn:string-length($text) idiv $length
208
let $tmp := fn:string-length($text) mod $length
211
for $i in (1 to $groupsNo)
212
return fn:substring($text, (1 + ($length*($i - 1))), $length)
214
for $i in (0 to $groupsNo)
217
fn:substring($text, 1, $tmp)
219
fn:substring($text, $tmp + ($length*($i - 1)) + 1, $length)
223
: Returns the given $text unchanged.
225
: @see http://office.microsoft.com/en-us/excel/HP052508361033.aspx
226
: @param $text the time
227
: @return The given $text unchanged.
229
declare function excel-text:asc
230
( $text as xs:string) as xs:string {
236
: Returns the character specified by a certain codepoint.
238
: @see http://office.microsoft.com/en-us/excel/HP052090091033.aspx
239
: @param $number the codepoint.
240
: @error XQP0021(errValue) provided $number must be in range [1,255].
241
: @return the character specified by a certain codepoint.
242
Zorba uses UTF-8 encoding so the actual codepoint range is between [1,1114111]
244
declare function excel-text:char
245
( $number as xs:integer) as xs:string {
247
if( (1 <= $number) and ($number <= 255)) then
248
fn:codepoints-to-string($number)
250
fn:error($excel-err:errValue, "Provided number must be in range [1,255]", $number)
254
: Returns a codepoint for the first character in a text string.
256
: @see http://office.microsoft.com/en-us/excel/HP052090151033.aspx
257
: @param $arg the string.
258
: @error XQP0021(errValue) Provided $arg was empty.
259
: @return A codepoint for the first character in a text string.
261
declare function excel-text:code
262
( $arg as xs:string) as xs:integer {
264
if( fn:string-length($arg) > 0 ) then
265
fn:string-to-codepoints(fn:substring($arg, 1, 1))
267
fn:error($excel-err:errValue, "Provided text was empty",$arg)
271
: Joins two text strings into one text string.
273
: @see http://office.microsoft.com/en-us/excel/HP052090151033.aspx
274
: @param $arg1 the first string.
275
: @param $arg2 the second string.
276
: @return Joins two text strings into one text string.
278
declare function excel-text:concatenate
279
( $arg1 as xs:anyAtomicType?,
280
$arg2 as xs:anyAtomicType?) as xs:string {
282
fn:concat($arg1,$arg2)
286
: Joins several text strings into one text string.
288
: @see http://office.microsoft.com/en-us/excel/HP052090151033.aspx
289
: @param $args a sequence of strings.
290
: @return Joins several text strings into one text string.
292
declare function excel-text:concatenate
293
( $args as xs:anyAtomicType*) as xs:string {
295
fn:string-join((for $value in $args return xs:string($value)), "")
299
: Removes all nonprintable characters from text.
301
: @see http://office.microsoft.com/en-us/excel/HP052090141033.aspx
302
: @param $arg the string.
303
: @return Removes all nonprintable characters from text. The CLEAN function was designed.
304
: to remove the first 32 nonprinting characters in the 7-bit ASCII code (values 0 through 31) from text. <br/>
305
: In the Unicode character set, there are additional nonprinting characters (values 127, 129, 141, 143, 144, and 157). <br/>
306
: By itself, the CLEAN function does not remove these additional nonprinting characters.
308
declare function excel-text:clean
309
( $arg as xs:string? ) as xs:string? {
311
fn:codepoints-to-string( for $codepoint in fn:string-to-codepoints($arg)
312
return if ($codepoint < 32) then () else $codepoint)
316
: Rounds a number to the specified number of decimals, formats the number in
317
: decimal format using a period and commas, and returns the result as text.
319
: @see http://office.microsoft.com/en-us/excel/HP052090931033.aspx
320
: @param $number is the number you want to round and convert to text.
321
: @param $decimals is the number of digits to the right of the decimal point.
322
: @return Rounds a number to the specified number of decimals, formats the number in
323
: decimal format using a period and commas, and returns the result as text.
325
declare function excel-text:fixed
326
( $number as xs:decimal,
327
$decimals as xs:decimal) as xs:string {
329
excel-text:fixed($number,$decimals,fn:false())
333
: Rounds a number to the specified number of decimals, formats the number in
334
:decimal format using a period and commas, and returns the result as text.
336
: @see http://office.microsoft.com/en-us/excel/HP052090931033.aspx
337
: @param $number is the number you want to round and convert to text.
338
: @param $decimals is the number of digits to the right of the decimal point.
339
: @param $no_commas is a logical value that, if TRUE, prevents FIXED from
340
:including commas in the returned text.
341
: @return Rounds a number to the specified number of decimals, formats the number in
342
:decimal format using a period and commas, and returns the result as text.
344
declare function excel-text:fixed
345
( $number as xs:decimal,
346
$decimals as xs:decimal,
347
$no_commas as xs:boolean) as xs:string {
349
let $tmp := excel-math:round($number, $decimals)
351
return if($no_commas) then
354
if(fn:contains(xs:string($tmp), '.')) then
355
let $x := fn:tokenize(xs:string($tmp),'[.]')[1]
356
let $y := fn:tokenize(xs:string($tmp),'[.]')[2]
357
let $x := fn:string-join(excel-text:tokenize-length($x,3), ',')
358
return fn:string-join(($x,$y),'.')
360
fn:string-join(excel-text:tokenize-length(fn:string($tmp),3), ',')
364
: Converts a number to text format and applies a currency symbol. The number of
365
:digits to the right of the decimal point is 2.
367
: @see http://office.microsoft.com/en-us/excel/HP052090931033.aspx
368
: @param $number is the number.
369
: @return Converts a number to text format and applies a currency symbol. The number of
370
:digits to the right of the decimal point is 2.
372
declare function excel-text:dollar
373
( $number as xs:decimal) as xs:string {
375
excel-text:dollar($number, 2)
379
: Converts a number to text format and applies a currency symbol.
381
: @see http://office.microsoft.com/en-us/excel/HP052090931033.aspx
382
: @param $number is the number.
383
: @param $decimals is the number of digits to the right of the decimal point. <br/>
384
:If decimals is negative, number is rounded to the left of the decimal point.
385
: @return Converts a number to text format and applies a currency symbol.
387
declare function excel-text:dollar
388
( $number as xs:decimal,
389
$decimals as xs:decimal) as xs:string {
391
let $int := fn:tokenize(fn:concat("$", excel-text:fixed(excel-math:abs($number), $decimals)),'\.')[1]
392
let $decimal := fn:tokenize(fn:concat("$", excel-text:fixed(excel-math:abs($number), $decimals)),'\.')[2]
394
return if( fn:empty($decimal) ) then $int
395
else fn:concat($int,'.',excel-text:pad-string-to-length($decimal,"0",$decimals))
399
: Compares two text strings and returns TRUE if they are exactly the same,
400
:FALSE otherwise. EXACT is case-sensitive but ignores formatting differences.
402
: @see http://office.microsoft.com/en-us/excel/HP052090811033.aspx
403
: @param $arg1 the first string.
404
: @param $arg2 the second string.
405
: @return Compares two text strings and returns TRUE if they are exactly the same,
406
:FALSE otherwise. EXACT is case-sensitive but ignores formatting differences.
408
declare function excel-text:exact
410
$arg2 as xs:string) as xs:boolean {
416
: Returns the first character in a text string.
418
: @see http://office.microsoft.com/en-us/excel/HP052091531033.aspx
419
: @param $arg is the text string that contains the characters you want to extract.
420
: @return The first character in a text string.
422
declare function excel-text:left
423
( $arg as xs:string) as xs:string {
424
excel-text:left($arg, 1)
428
: Returns the first character or characters in $text, based on the number of $num_chars you specify.
430
: @see http://office.microsoft.com/en-us/excel/HP052091531033.aspx
431
: @param $text is the text string that contains the characters you want to extract.
432
: @param $num_chars specifies the number of characters you want to extract.
433
: @return The first character or characters in $text, based on the number of $num_chars you specify.
435
declare function excel-text:left
436
( $text as xs:string,
437
$num_chars as xs:integer) as xs:string {
439
let $chars := if (fn:string-length($text) < $num_chars) then
440
fn:string-length($text)
444
return fn:substring($text, 1, $chars)
448
: Returns the number of characters in a text string.
450
: @see http://office.microsoft.com/en-us/excel/HP052091541033.aspx
451
: @param $arg the string.
452
: @return The number of characters in a text string.
454
declare function excel-text:len
455
( $arg as xs:string?) as xs:integer {
457
fn:string-length($arg)
461
: Converts all uppercase letters in a text string to lowercase.
463
: @see http://office.microsoft.com/en-us/excel/HP052091671033.aspx
464
: @param $arg the string.
465
: @return Converts all uppercase letters in a text string to lowercase.
467
declare function excel-text:lower
468
( $arg as xs:string?) as xs:string? {
474
: Returns a specific number of characters from a text string, starting at
475
:the position you specify, based on the number of characters you specify.
477
: @see http://office.microsoft.com/en-us/excel/HP052091751033.aspx
478
: @param $text the text string containing the characters you want to extract.
479
: @param $start_num the position of the first character you want to extract
480
: in text. The first character in text has start_num 1, and so on.
481
: @param $num_chars the number of characters you want to return from text.
482
: @return A specific number of characters from a text string, starting at
483
: the position you specify, based on the number of characters you specify.
485
declare function excel-text:mid
486
( $text as xs:string?,
487
$start_num as xs:integer,
488
$num_chars as xs:integer) as xs:string? {
489
let $len := fn:string-length($text)
491
if ($start_num > $len) then
493
else if ($start_num < 1) then
494
fn:error($excel-err:errValue, "Provided value is less than 1", $start_num)
495
else if ($num_chars < 0) then
496
fn:error($excel-err:errValue, "Provided value is less than zero", $num_chars)
498
fn:substring($text, $start_num, $num_chars)
502
: Replaces part of a text string, based on the number of characters you specify, with a different text string.
504
: @see http://office.microsoft.com/en-us/excel/HP052092351033.aspx
505
: @param $old_text is text in which you want to replace some characters.
506
: @param $start_num the position of the character in old_text that you want to replace with new_text.
507
: @param $num_chars the number of characters in old_text that you want REPLACE to replace with new_text.
508
: @param $new_text the text that will replace characters in old_text.
509
: @return Replaces part of a text string, based on the number of characters you specify, with a different text string.
511
declare function excel-text:replace
512
( $old_text as xs:string?,
513
$start_num as xs:integer,
514
$num_chars as xs:integer,
515
$new_text as xs:string) as xs:string {
517
fn:concat(fn:substring($old_text,0,$start_num),
519
fn:substring($old_text,$start_num + $num_chars))
523
: Returns the last character in a text string.
525
: @see http://office.microsoft.com/en-us/excel/HP052092371033.aspx
526
: @param $arg the text string containing the characters you want to extract.
527
: @return The last character in a text string.
529
declare function excel-text:right
530
( $arg as xs:string) as xs:string {
532
excel-text:right($arg, 1)
536
: Returns the last character or characters in a text string, based on the number of characters you specify.
538
: @see http://office.microsoft.com/en-us/excel/HP052092371033.aspx
539
: @param $text the text string containing the characters you want to extract.
540
: @param $num_chars specifies the number of characters you want RIGHT to extract.
541
: @return The last character or characters in a text string, based on the number of characters you specify.
543
declare function excel-text:right
544
( $text as xs:string,
545
$num_chars as xs:integer) as xs:string {
547
let $chars := if (fn:string-length($text) < $num_chars) then fn:string-length($text) else $num_chars
548
return fn:substring($text, (fn:string-length($text) - $chars + 1), $chars)
552
: Locate one text string within a second text string, and return the number of
553
: the starting position of the first text string from the first character of the
554
: second text string. <br/> The search starts at position 1, and it is not case sensitive.
556
: @see http://office.microsoft.com/en-us/excel/HP052092491033.aspx
557
: @param $find_text text you want to find.
558
: @param $within_text text in which you want to search for $find_text.
559
: @error XQP0021(errValue) the value is not greater than zero or is greater than the length of within_text.
560
: @error XQP0021(errValue) value was not found.
561
: @return Locate one text string within a second text string, and return the number of
562
: the starting position of the first text string from the first character of the
563
: second text string. <br/> The search starts at position 1, and it is not case sensitive.
565
declare function excel-text:search
566
( $find_text as xs:string,
567
$within_text as xs:string) as xs:integer? {
569
excel-text:search($find_text, $within_text, 1)
573
: Locate one text string within a second text string, and return the number of
574
: the starting position of the first text string from the first character of the
575
: second text string.<br/> The search starts at $start_num, and it is not case sensitive.
577
: @see http://office.microsoft.com/en-us/excel/HP052092491033.aspx
578
: @param $find_text text you want to find.
579
: @param $within_text text in which you want to search for $find_text.
580
: @param $start_num the character number in within_text at which you want to start searching.
581
: @error XQP0021(errValue) the value is not greater than zero or is greater than the length of within_text.
582
: @error XQP0021(errValue) value was not found.
583
: @return Locate one text string within a second text string, and return the number of
584
: the starting position of the first text string from the first character of the
585
: second text string.<br/> The search starts at $start_num, and it is not case sensitive.
587
declare function excel-text:search
588
( $find_text as xs:string,
589
$within_text as xs:string,
590
$start_num as xs:integer) as xs:integer? {
592
if(($start_num < 1) or ($start_num > fn:string-length($within_text))) then
593
fn:error($excel-err:errValue, "The value is not greater than zero or is greater than the length of within_text", $start_num)
595
let $source := fn:substring($within_text, $start_num)
597
return if(fn:exists(excel-text:index-of-match-first($source, $find_text, "i"))) then
598
$start_num + excel-text:index-of-match-first($source, $find_text, "i") -1
600
fn:error($excel-err:errValue, "Value was not found", $find_text)
604
: Locate one text string within a second text string, and return the number of the
605
: starting position of the first text string from the first character of the second text string. <br/>
606
: The search is case sensitive.
608
: @see http://office.microsoft.com/en-us/excel/HP052090891033.aspx
609
: @param $find_text text you want to find.
610
: @param $within_text text in which you want to search for $find_text.
611
: @error XQP0021(errValue) the value is not greater than zero or is greater than the length of within_text.
612
: @error XQP0021(errValue) value was not found.
613
: @return Locate one text string within a second text string, and return the number of the
614
: starting position of the first text string from the first character of the second text string. <br/>
615
: The search is case sensitive.
617
declare function excel-text:find
618
( $find_text as xs:string,
619
$within_text as xs:string) as xs:integer? {
621
excel-text:find($find_text, $within_text, 1)
625
: Locate one text string within a second text string, and return the number of the
626
: starting position of the first text string from the first character of the second text string.<br/>
627
: The search is case sensitive.
629
: @see http://office.microsoft.com/en-us/excel/HP052090891033.aspx
630
: @param $find_text text you want to find.
631
: @param $within_text text in which you want to search for $find_text.
632
: @param $start_num specifies the character at which to start the search.
633
: @error XQP0021(errValue) the value is not greater than zero or is greater than the length of within_text.
634
: @error XQP0021(errValue) value was not found.
635
: @return Locate one text string within a second text string, and return the number of the
636
: starting position of the first text string from the first character of the second text string.<br/>
637
: The search is case sensitive.
639
declare function excel-text:find
640
( $find_text as xs:string,
641
$within_text as xs:string,
642
$start_num as xs:integer) as xs:integer? {
644
if(($start_num < 1) or ($start_num > fn:string-length($within_text))) then
645
fn:error($excel-err:errValue, "The value is not greater than zero or is greater than the length of within_text", $start_num)
647
let $source := fn:substring($within_text, $start_num)
649
return if(fn:exists(excel-text:index-of-match-first($source, $find_text))) then
650
$start_num + excel-text:index-of-match-first($source, $find_text) -1
652
fn:error($excel-err:errValue, "Value was not found", $find_text)
656
: Substitutes new_text for old_text in a text string.
658
: @see http://office.microsoft.com/en-us/excel/HP052092861033.aspx
659
: @param $text the text or the reference to a cell containing text for which you want to substitute characters.
660
: @param $old_text text you want to replace.
661
: @param $new_text text you want to replace old_text with.
662
: @param $instance_num specifies which occurrence of old_text you want to replace with new_text. <br/>
663
: Only that instance of old_text is replaced.
664
: @return Substitutes new_text for old_text in a text string. <br/> Use SUBSTITUTE when you
665
: want to replace specific text in a text string; use REPLACE when you want
666
: to replace any text that occurs in a specific location in a text string.
668
declare function excel-text:substitute
669
( $text as xs:string,
670
$old_text as xs:string,
671
$new_text as xs:string,
672
$instance_num as xs:integer?) as xs:string {
674
let $startPos := excel-text:index-of-match($text,$old_text,1,$instance_num)
676
if(fn:empty($startPos)) then $text
677
else excel-text:replace($text, $startPos, fn:string-length($old_text), $new_text)
681
: Substitutes new_text for old_text in a text string. Every occurrence of old_text in text is changed to new_text.
683
: @see http://office.microsoft.com/en-us/excel/HP052092861033.aspx
684
: @param $text the text or the reference to a cell containing text for which you want to substitute characters.
685
: @param $old_text text you want to replace.
686
: @param $new_text text you want to replace old_text with.
687
: @return Substitutes new_text for old_text in a text string. Every occurrence of old_text in text is changed to new_text.
689
declare function excel-text:substitute
690
( $text as xs:string,
691
$old_text as xs:string,
692
$new_text as xs:string) as xs:string? {
694
fn:replace($text, $old_text, $new_text)
698
: Removes all spaces from text except for single spaces between words.
700
: @see http://office.microsoft.com/en-us/excel/HP052093211033.aspx?pid=CH062528321033
701
: @param $text from which you want spaces removed.
702
: @return Removes all spaces from text except for single spaces between words.
704
declare function excel-text:trim
705
( $text as xs:string?) as xs:string? {
707
fn:normalize-space($text)
711
: Converts text to uppercase.
713
: @see http://office.microsoft.com/en-us/excel/HP052093271033.aspx
714
: @param $text text you want converted to uppercase.
715
: @return Converts text to uppercase.
717
declare function excel-text:upper
718
( $text as xs:string?) as xs:string? {
724
: Converts the $value to string.
726
: @see http://office.microsoft.com/en-us/excel/HP052093041033.aspx
727
: @param $value the value
728
: @return Converts the $value to string.
730
declare function excel-text:t
731
( $value as xs:anyAtomicType?) as xs:string {
733
if( $value instance of xs:string ) then fn:string($value)
738
: Converts a text string that represents a number to a number.
740
: @see http://office.microsoft.com/en-us/excel/HP052093291033.aspx
741
: @param $arg the value.
742
: @error XQP0021(errValue) provided value is not a number.
743
: @return Converts a text string that represents a number to a number.
745
declare function excel-text:value
746
( $arg as xs:anyAtomicType?) as xs:anyAtomicType? {
749
case xs:double return $arg
750
case xs:decimal return $arg
751
case xs:double return $arg
752
case xs:float return $arg
754
if($arg castable as xs:integer) then
757
if($arg castable as xs:decimal) then
760
if($arg castable as xs:double) then
763
fn:error($excel-err:errValue, "Provided value is not a number", $arg)
added
removed
src/com/zorba-xquery/www/modules/excel/text.xq
