Filter
Top Products
INDEX
POS is the preferred built-in function for obtaining the position of one string in another. See page 189 for a
complete description.
INDEX(haystack,needle
,start
)
returns the character position of one string, needle, in another, haystack, or returns 0 if the string needle is
not found or is a null string. By default the search starts at the first character of haystack (start has the
value 1). You can override this by specifying a different start point, which must be a positive whole number.
Here are some examples:
INDEX('abcdef','cd') -> 3
INDEX('abcdef','xd') -> 0
INDEX('abcdef','bc',3) -> 0
INDEX('abcabc','bc',3) -> 5
INDEX('abcabc','bc',6) -> 0
INSERT
INSERT(new,target
,
n ,
length ,pad
)
inserts the string new, padded or truncated to length length, into the string target after the nth character.
The default value for n is 0, which means insert before the beginning of the string. If specified, n and
length must be positive whole numbers or zero. If n is greater than the length of the target string, padding
is added before the string new also. The default value for length is the length of new.Iflength is less than
the length of the string new, then INSERT truncates new to length length. The default pad character is a
blank.
Here are some examples:
INSERT(' ','abcdef',3) -> 'abc def'
INSERT('123','abc',5,6) -> 'abc 123 '
INSERT('123','abc',5,6,'+') -> 'abc++123+++'
INSERT('123','abc') -> '123abc'
INSERT('123','abc',,5,'-') -> '123--abc'
JUSTIFY
JUSTIFY(string,length
,pad
)
returns string formatted by adding pad characters between blank-delimited words to justify to both margins.
This is done to width length (length must be a positive whole number or zero). The default pad character
is a blank.
The first step is to remove extra blanks as though SPACE(string) had been run (that is, multiple blanks are
converted to single blanks, and leading and trailing blanks are removed). If length is less than the width of
the changed string, the string is then truncated on the right and any trailing blank is removed. Extra pad
characters are then added evenly from left to right to provide the required length, and the pad character
replaces the blanks between words.
Here are some examples:
JUSTIFY('The blue sky',14) -> 'The blue sky'
JUSTIFY('The blue sky',8) -> 'The blue'
JUSTIFY('The blue sky',9) -> 'The blue'
JUSTIFY('The blue sky',9,'+') -> 'The++blue'
Functions
Chapter 14. Functions 187