string functions

QlikView Expressor string functions include all of the string manipulation functions contained in Lua string library as well as additional functions. While some QlikView Expressor function names appear to be aliases for existing string functions (e.g. repeat as an alias for rep or substring as an alias for sub), this interpretation is too simplistic. The string functions contain error messages that are specific to QlikView Expressor.  Refer to the functional descriptions in this manual.

The string functions include:

allow

bit

byte

char

concatenate

datetime

duplicate

filter

find

format

frequency

insert

iterate

leftpad

lefttrim

length

lower

match

metaphone

replace

reverse

rightpad

righttrim

soundex

squeeze

substring

title

trim

upper

. .

Several of the string functions employ pattern matching capabilities.

Return to Reference: Datascript Module Editing

allow

allow operates on a string field, returning a string containing only the "allowed" characters.

usage

 string string.allow(value, allowed)

arguments

value

byte or string field

allowed

subset of characters to return

return

 string

Examples

string.allow("George W. Bush", "GWB")

Returns the string GWB.

string.allow("I.B.M.", "IBM")

Returns the string IBM.

string.allow(nil, "IBM")

Returns nil.

Return to top

bit

bit operates on a string field returning a bit set. The string field can optionally use characters to represent the numerics 0 and 1. In QlikView Expressor, bit sets contain 64 bits. The right-most bit is bit position 1 and the left-most bit is bit position 64.

usage

 bit set string.bit(value[, format])

arguments

value

byte or string field

format

the characters representing 0 and 1

return

 bit set

Examples

string.bit("1101")

Returns the bit set  0000000000000000000000000000000000000000000000000000000000001101.

string.bit("TFTF", "TF")

Returns the bit set 0000000000000000000000000000000000000000000000000000000000001010.

Return to top

byte

byte operates on a string field, returning the internal numerical codes for the characters. If both start and end are specified, it returns an integer[]. To capture the elements of a multi-element array, assign the return to multiple variables or use the select function to specify a single element.

usage

 integer[] string.byte(value[, begin[,end]])

arguments

value

byte or string field

begin

optional positional offset in value of the first character to return.
Default is 1, negative entries offset from the right end of
value

end

optional positional offset of last character to return.
Default is start

return

 integer[]

Examples

string.byte("abcd")

Returns the single element array: 97, which corresponds to the character "a."

string.byte("abcd", 2)

Returns the single element array: 98, which corresponds to the character "b."

var1, var2 = string.byte("abcd", 1, 2)

Returns the two element array: 97, 98, which are assigned to the variables var1 and var2 respectively.

var2 = select(2, string.byte("abcd", -2, -1))

Returns the two element array: 99, 100, which correspond to the characters "c" and "d" respectively, but the select function extracts only the second element (100, "d") into the variable var2.

Note:  Passing nil returns nil.

Return to top

char

char operates on multiple byte codes, returning the corresponding character string.

usage

 string string.char(integer[, ...])

arguments

integer

comma separated list of byte codes to be converted into alphanumeric characters

only the values 0 through 127 can be specified; nil cannot be used as a value

return

 string

Examples

string.char(97, 98, 99, 100)

Returns the string abcd.

string.char(65, 66, 67)

Returns the string ABC.

Note:  Passing nil returns nil.

Return to top

concatenate

concatenate operates on a list of fields, returning a concatenated string.

usage

 string string.concatenate(value[, ...)

arguments

value

fields to be concatenated.
fields can be any type convertible to a string

return

 string

Examples

string.concatenate("expressor", " ", "software")

Returns the string expressor software.

string.concatenate(77, "+", 78)

Returns the string 77+78. Notice the numeric fields are converted to string types and then concatenated.

Note:  All fields are converted to string. A nil argument is ignored and the remaining arguments concatenated.

Return to top

datetime

datetime operates on a string field, returning an unformatted datetime as the number of seconds since January 1, 2000.

usage

 datetime string.datetime(value[, format])

arguments

value

string representation of a datetime

format

datetime format specification of value (not the format of the return value)

the default format is CCYY-MM-DD HH24:MI:SS

the format is optional only when value has the default format

Format

Interpretation

HH24

hours in 24 hour format

H*24

hours in 24 hour format

one digit hour formatting when appropriate

HH12

hours in 12 hour format

H*12

hours in 12 hour format

one digit hour formatting when appropriate

HH

hours in 24 hour format

H*

hours in 24 hour format

one digit hour formatting when appropriate

MI

minutes

SS

seconds

s[ssssss]

fractional seconds

AM or PM

used with HH or HH12 to indicate whether hour values are AM or PM

only valid if a full time format, including fractional seconds, is specified

this value is passed to the output

DD

day

D*

day specified as either one or two digits

format pattern must be delimited, i.e.
MM-D*-CCYY or MM/D*/CCYY, not MMD*CCYY

valid format delimiters are space, hyphen, forward slash, comma and period

D?

invalid day specification accepted; converts the day to either 01 or the last day of the month based on the input value

DM

allows processing of mixed day/month, giving precedence to day

used in conjunction with the MD format

DDD

day of week abbreviated

DAY

day of week abbreviated

DDDD

day of week in long format

DDAY

day of week in long format

JJJ

Julian day of year

MM

month

M*

month specified as either one or two digit

format pattern must be delimited, i.e.
M*-DD-CCYY or M*/DD/CCYY, not M*DDCCYY

valid format delimiters are space, hyphen, forward slash, comma and period

M?

invalid month specification accepted

MD

allows processing of mixed month/day, giving precedence to month

used in conjunction with the DM format

MMM

month in short format (e.g., JAN)

MMMM

month in long format (e.g., January)

YY

years

YNN

forces a century designation anchored to NN

in a date field, a two character year is interpreted as the current century if less than NN and the previous century if greater than NN

CC

century

return

 datetime

Examples

string.datetime("2005-01-12 16:30:30")

Returns a datetime field containing 158862630, 2005-01-12 16:30:30 as the number of seconds since January 1, 2000.

string.datetime("05-012 16:30", "YY-JJJ HH24:MI")

Returns a datetime field containing 158862630, 05-012 16:30 as the number of seconds since January 1, 2000. Notice how the second argument describes the format of the first argument.

string.datetime("05-01-12", "Y09-MM-DD")

Returns a datetime field containing the date 2005-01-12, January 12, 2005. The year 05 is interpreted as the current century, 2005, since it is less than 09.

string.datetime(nil)

Returns nil.

Return to top

duplicate

duplicate operates on a string field, returning the concatenation of a specified number of repeats of the string.

usage

 string string.duplicate(value, count)

arguments

value

string field or field (e.g. an integer) that can be converted into a string field

count

the number of times to concatenate the string to itself

return

 string

Examples

string.duplicate(9, 9)

Returns the string 999999999. Notice the numeric value has been converted into a string.

string.duplicate("*", 4)

Returns the string ****.

Note:  Passing nil returns nil.

Return to top

filter

filter operates on a string field, returning a string from which "filtered" characters have been removed.

usage

 string string.filter(value, filter)

arguments

value

byte or string field

filter

subset of characters to remove

return

 string

Examples

string.filter("George W. Bush", "BgG.")

Returns the string eore W ush. Notice that multiple characters can be filtered and that the order of the characters to be filtered can be random.

string.filter(nil)

Returns nil.

Return to top

find

find operates on a string field, returning the starting and ending character positions of a specified character pattern.  If the pattern has subpatterns (referred to as captures), the characters in the subpattern are also returned.

usage

 integer[] string.find
(value, pattern[, begin[,off]])

arguments

value

byte or string field

pattern

character pattern to find

begin

optional starting point offset in value. If negative, offset search from right end of value and perform search from left to right

off

if true, turn off character class capabilities in pattern and the function does a basic find substring operation

return

 integer, integer[, capture[, ...]]

Examples

var1, var2 = string.find("Hello all users", "all")

Begins its pattern search from position 1 (H). Returns the starting position 7, into var1, and the ending position 9 into var2.

var1, var2 = string.find("Hello all users", "e", -5)

Begins its pattern search from the fifth position from the string's right end, and searches from left to right.  Returns the starting position 13 and the ending position 13, the position of the e character in the word users into both var1 and var2.

var1, var2 = string.find("Hello all users", "%su")

Begins its pattern search from position 1 (H). Returns the starting position 10, into var1, and the ending position 11, into var2, corresponding to the space before, and the u character in, the word users.

var1, var2 = string.find("Hello all users", "%su", 1, true)

The fourth argument turns off pattern matching. Returns nil into var1 and var2.

var1, var2, var3 = string.find("Hello all users", ".([lo]+)")

Begins its pattern search from position 1 (H). Returns the starting position 2 into var1, the ending position 5 into var2, and the capture llo into var3.

var1, var2, var3 = string.find("Hello all users", ".([l]+)")

Begins its pattern search from position 1 (H). Returns the starting position 2 into var1, the ending position 4 into var2, and the capture ll. into var3.

In the two preceding examples, notice the interpretations of the two patterns. The character class [lo]+ is interpreted as one or more "l" or "o" characters, which makes the "e"character the start of the pattern and the space following "Hello"  the end of the pattern. The character class [l]+ is interpreted as one or more "l" characters, which also starts the pattern at the "e" character but ends the pattern at the "o" character.

var1 = 0

while true do

    var1 = string.find("Hello all users", "%s", var1+1)

    if var1 == nil then break end

    -- other code using var1

end

Begins its pattern search from position 1 (H) and iterates through the sting finding each space character and placing its index into var1. This example was derived from an example in the book version of the Lua reference manual (Ierusalimschy, R., Programming in Lua, 2nd edition, Lua.org, Rio de Janeiro, 2006).

Note:  If off is specified, start must also be specified.
Pattern matching guidelines are described in QlikView Expressor pattern guidelines.
Passing nil returns nil.

Return to top

format

format returns a formatted version of its variable number of arguments. The format of the return value is described in the first argument, which must be a string.

usage

 string string.format(formatstring, ...)

arguments

formatstring

the formatting to be applied to the remaining arguments. The format string uses the same terminology as the C function printf

the options c, d, E, e, f, g, G, i, o, u, X, and x require numbers as arguments; q and s require string arguments

...

one, or more, arguments that are returned as a string formatted as per formatstring

return

 string

Examples

string.format("%04d", 5)

Returns the string 0005 (the numeric argument has been left padded with zeros to create a four character string.

string.format("%s%s", "hello ", "expressor user")

Returns the string hello expressor user.

Note: The function does not accept string values containing embedded zeroes except as an argument to a formatstring containing the q option. Passing nil returns nil.

Return to top

frequency

frequency operates on a string field, returning the number of occurrences of a specified character.

usage

 integer string.frequency(value, character)

arguments

value

string field or field (e.g. an integer) that can be converted into a string field

character

character to determine frequency.
Although character can be a string, only the frequency is determined for the first character

return

 integer

Examples

string.frequency("expressor", "s")

Returns the integer 2.

string.frequency("apple     pie", " ")

Returns the integer 5 (the number of spaces between apple and pie.

string.frequency(000001234, "0")

Returns the integer 0. Notice that for the analysis, the numeric 000001234 was transparently converted into the string 1234.

string.frequency("000001234", "0")

Returns the integer 5. Notice that for the analysis, leading zeros in the string numeric are not removed.

string.frequency(nil, "a")

Returns the integer 0.

Return to top

insert

insert operates on a string field, returning a string with another embedded string at a specified position.

usage

 string string.insert(value, insert[, begin])

arguments

value

string field

insert

string to insert

begin

optional position in value after which to embed insert. If unspecified, the insertion point is at the beginning of value

return

 string

Examples

string.insert("pressor", "ex")

Returns the string expressor.

string.insert("expreor", "ss", 5)

Returns the string expressor.

string.insert("hello users", "all ", 6)

Returns the string hello all users.

Note: Passing nil returns nil.

Return to top

iterate

iterate operates on a string field using an iterator function to return all occurrences of the specified pattern in the string.

usage

 string string.iterate(value, pattern)

arguments

value

the string to examine

pattern

a pattern to find in value

return

 string

Note: The function does not accept string values containing embedded zeroes except as an argument to a formatstring containing the q option. Passing nil returns nil.

Return to top

leftpad

leftpad operates on a string field, returning the string padded at the left end to a specified length with a defined character. You can also use lpad.

usage

 string string.leftpad(value, length[, character])

arguments

value

string field or field (e.g. an integer) that can be converted into a string field

length

final length of padded string. If the length of the starting string is greater than length, the starting string is returned unaltered

character

the padding character. Default is space

return

 string

Examples

string.leftpad("expressor", 12)

Returns the string    expressor (the initial string preceded by three space characters).

string.leftpad("expressor", 12, "*")

Returns the string ***expressor.

string.leftpad("expressor", 2)

Returns the string expressor (since the starting string is greater than the specified padded string, the starting string is returned).

string.leftpad(1234, 5, "0")

Returns the string 01234.

string.leftpad(nil, 5, "a")

Returns nil.

Return to top

lefttrim

lefttrim operates on a string field, returning the string trimmed of space characters from the left end up to the first character that is not a space. You can also use ltrim.

usage

 string string.lefttrim(value)

arguments

value

string field or field (e.g. an integer) that can be converted into a string field

return

 string

Examples

string.lefttrim("   expressor   ")

Returns the string expressor    .  Only the space characters to the left of the starting string are removed.

string.lefttrim(string.concatenate(" Hello", " world"))

Returns the string Hello world.  Only the space character to the left of the concatenated string is removed. The space character at the left end of world is internal to the concatenated string and is not removed.

string.lefttrim(nil)

Returns nil.

Return to top

length

length operates on a string field, returning the length of the string.

usage

 string string.length(value)

arguments

value

string field

return

 integer

Examples

string.length("expressor")

Returns 9.

string.length(string.concatenate("Hello", " world"))

Returns 11.

Note: The empty string has length zero. Passing nil returns nil.

Return to top

lower

lower operates on a string field, returning the string with all upper case characters converted to lower case. Lower case characters are returned unaltered.

usage

 string string.lower(value)

arguments

value

string field

return

 string

Examples

string.lower("EXPRESSOR")

Returns expressor.

string.lower(string.concatenate("HELLO", " World"))

Returns hello world.

Note: Passing nil returns nil.

Return to top

match

match operates on a string field looking for the first match of a specified pattern. If a match is identified, the function returns the characters matching the pattern.

usage

 string string.match(value, pattern[, begin])

arguments

value

string field to search

pattern

pattern to find

begin

optional start position at which to begin the search. Default is 1.
Negative values are offsets from the right end of value

return

 string

Examples

string.match("hello all users", "hello")

Returns the string hello.

string.match("hello all users", "hello", 5)

Returns nil because the search begins with character 5, which is beyond the matching string.

string.match("Today is Tuesday, 11/13/2007", "%d+/%d+/%d+")

string.match("Today is Tuesday, 1/11/08", "%d+/%d+/%d+")

The first function call returns 11/13/2007 while the second function call returns 1/11/08. Notice how the pattern successfully selects the date content from the string without any pre-existing knowledge of the number of characters used to represent month, day or year. The pattern %d+ specifies one or more characters for each representation.

Note: Passing nil returns nil.

Return to top

metaphone

metaphone operates on a string field, returning a key string using a metaphone phonetic algorithm. Similar sounding strings return the same keys. Keys are variable length and composed of upper case alphabetic characters.

usage

 string string.metaphone(value)

arguments

value

string to be encoded

return

 string with the pattern %u+

Examples

string.metaphone("Gammon")

Returns KMN.

string.metaphone("gamon")

Returns KMN.

string.metaphone("gamin")

Returns KMN.

string.metaphone("Cameron")

Returns KMRN.

string.metaphone("bruise")

Returns BRS.

string.metaphone("Bruce")

Returns BRS.

string.metaphone(nil)

Returns nil.

Return to top

replace

replace operates on a string field, returning a string with occurrences of a specified character sequence replaced with another character sequence. The number of occurrences replaced is also returned.

usage

 string string.replace(value, old, new[, count])

arguments

value

string field or field (e.g. an integer) that can be converted into a string field

old

the character sequence to be replaced

new

the character sequence to be inserted

count

the number of times to replace the character string old

return

 string, integer

Examples

string.replace("expreSSor", "SS", "ss")

Returns the string expressor.

string.replace(12345, "23", "99")

Returns the string 19945. Notice the numeric value 12345 has been converted into a string type.

Note:

If new is a string, its value is used for replacement. The character % works as an escape character:

If new is a function, this function is called every time a match occurs, with all captured substrings passed as arguments in order. If the pattern specifies no captures, the whole match is passed as a sole argument.

Passing nil returns nil.

Return to top

reverse

reverse operates on a string field, returning a string with the order of characters reversed.

usage

 string string.reverse(value)

arguments

value

ring field or field (e.g. an integer) that can be converted into a string field

return

 string

Examples

string.reverse("expressor")

Returns the string rosserpxe.

string.reverse(12345)

Returns the string 54321. Notice the numeric value 12345 has been converted into a string type.

Note:  Passing nil returns nil.

Return to top

rightpad

rightpad operates on a string field, returning the string padded at the right end to a specified length with a specified character. You can also use rpad.

usage

 string strangleholds, length[, character])

arguments

value

string field or field (e.g. an integer) that can be converted into a string field

length

final length of padded string. If the length of the starting string is greater than length, the starting string is returned unaltered

character

the padding character. Default is space

return

 string

Examples

string.rightpad("expressor", 12)

Returns the string expressor    (the initial string followed by three space characters).

string.rightpad("expressor", 12, "*")

Returns the string expressor***.

string.rightpad("expressor", 2)

Returns the string expressor (since the starting string is greater than the specified padded string, the starting string is returned).

string.rightpad(1234, 5, "0")

Returns the string 12340.

string.rightpad(nil, 5, "a")

Returns nil.

Return to top

righttrim

righttrim operates on a string field, returning the string trimmed of space characters from the right up to the first character that is not a space. You can also use rtrim.

usage

 string string.righttrim(value)

arguments

value

string field or field (e.g. an integer) that can be converted into a string field

return

 string

Examples

string.righttrim("   expressor   ")

Returns the string    expressor.  Only the space characters to the right of the starting string are removed.

string.righttrim(string.concatenate(" Hello ", "world "))

Returns the string  Hello world.  Only the space character to the right of the concatenated string is removed. The space character at the right end of Hello is internal to the concatenated string and is not removed.

string.righttrim(nil)

Returns the nil.

Return to top

soundex

soundex operates on a string field creating a Soundex code, which is used to compare strings with the same pronunciation but with different spellings. Generally an encoding consists of four code points: the string's first character followed by three numeric characters representing the string's remaining consonants (see Wikipedia — Soundex). The QlikView Expressor soundex function returns a Soundex code containing a specified number of code points, which is not necessarily a multiple of four characters.

usage

 string string.soundex(value[, points])

arguments

value

string to be converted into a Soundex code

points

optional specification of the number of code points;
not necessarily a multiple of 4

return

 string with the pattern %u%d+

Examples

string.soundex("expressor", 4)

Returns E216. Notice that 4 code points is not sufficient to fully represent the string. When invoked with 6 code points, the function returns E21626, which includes points for all of the string's consonants, and 8 code points return E2162600, which is longer than necessary.

string.soundex("parallel", 4)

Returns P644. This Soundex code is a complete representation of the string.

string.soundex("expressor parallel", 8)

Returns E2162616. As with the first example, the number of code points is not sufficient to fully represent the string; 10 code points generates E216261644, which is of a sufficient length.

string.soundex(nil)

Returns nil.

Return to top

squeeze

squeeze operates on a string field, returning the string with duplicate adjacent characters removed.

usage

 string string.squeeze(value[, character])

arguments

value

string field or field (e.g. an integer) that can be converted into a string field

character

the character to remove. Default is the space character

return

 string

Examples

string.squeeze("expressor", "s")

Returns the string expresor.

string.squeeze("apple   pie")

Returns the string apple pie.

string.squeeze(00001234, "0")

Returns the string 01234.  Notice the numeric value 00001234 has been converted into a string type.

string.squeeze(nil)

Returns nil.

Return to top

substring

substring operates on a string field, returning a substring.

usage

 string string.substring(value[, begin[, end])

arguments

value

byte or string field

begin

optional positional offset into value,default is 1.
If negative offset from right end of
value

end

optional positional offset of last character to return.
Default is to the last character of value

return

 string

Examples

string.substring("expressor", 2, 2)

Returns the string x.

string.substring("expressor", 2)

Returns the string xpressor.

string.substring("expressor", -3)

Returns the string sor.

string.substring("expressor", -5, -4)

Returns the string es.

Note:  Passing nil returns nil.

Return to top

title

title operates on a string field, returning the string converted to title format.

usage

 string string.title(value)

arguments

value

string field

return

 string

Examples

string.title("expressor")

Returns the string Expressor.

string.title("mR. jones")

Returns the string Mr. Jones.

string.title(string.concatenate("mR.", " ", "SMITH"))

string.title(string.concatenate("dr.", "BILL")

The first invocation returns the string Mr. Smith and the second invocation returns Dr.bill. The space character is essential for proper formatting of the last parameter.

Note:  Passing nil returns nil.

Return to top

trim

trim operates on a string field, returning the string with space characters removed from the left and right ends.

usage

 string string.trim(value)

arguments

value

string field

return

 string

Examples

string.trim("   expressor   ")

Returns the string expressor.

string.title(string.concatenate("  hello ", "world  "))

Returns the string hello world. The space character at the right end of hello is internal to the concatenated string and is not removed.

string.trim(nil)

Returns nil.

Return to top

upper

upper operates on a string field, returning the string with all lower case characters converted to upper case. Upper case characters are returned unaltered.

usage

 string string.upper(value)

arguments

value

string field

return

 string

Examples

string.upper("expressor")

Returns EXPRESSOR.

string.upper(string.concatenate("hello", " World"))

Returns HELLO WORLD.

Note:  Passing nil returns nil.

Return to top

 

. .

The string concatenation operator denoted by two dots ('..') operates on two operands that are strings or numbers. The operands are converted to strings. When a number is used where a string is expected, the number is converted to a string, in a reasonable format. For complete control over how numbers are converted to strings, use the see string.format.

usage

 .. value ..

arguments

value

string or number field

return

 string

Examples

message = "The" ..funcName.. "function failed with return code" ..retCode

Returns The exit function failed with the return code 0014

Return to top