String Functions

Spectre string functions operate on a string value to create another string value. These functions can be used in all Spectre file types.

NOTE: String manipulations are best done at build time rather than at query time. In other words, it is a best practice to use string functions to create extra columns in a Build script, rather than in a cPlan or Dive script.

Uppercase the first character of each word in the specified string and lowercase the other characters.
capitalize(string : string) : string

For example:

`capitalize("relationship")` returns Relationship

`capitalize("john f. kennedy")` returns John F. Kennedy

`capitalize("let's get it started")` returns Let's Get It Started

`capitalize("PLEASE STOP SHOUTING")` returns Please Stop Shouting

Returns a single-character string with the character represented by the number value in the current character set (ASCII or EBCDIC).
chr(integer : integer) : string

For example (using ASCII):

`chr(65) returns A

`chr(32) returns space

`chr(92) returns \

Concatenate a sequence of strings.
concat(string1 : string, string2 : string, ... : string) : string

For example:

`concat("ab", "c", " ", "defg")` returns abc defg

concat("Smith", ", ", "John") returns Smith, John

Compute the Damerau-Levenshtein distance between two strings.
damerau(string1 : string, string2 : string) : integer
This is a similarity measure between two strings. It takes two strings and produces the number of edits that turns the first string into the second string. This comparison is almost the same as the Levenshtein distance except that the Damerau-Levenshtein distance also allows transposition of two adjacent characters as the edit operations it counts. See http://en.wikipedia.org/wiki/Damerau%E2%80%93Levenshtein_distance

For example:

`damerau("kitten", "sitting")` returns 3

`damerau("Quiet Clam", "Quite Calm")` returns 2

Perform double metaphone phonetic string comparison.
dmetaphone(string1 : string, string2: string) : string
This is a phonetic comparison that tries to return true when two strings would sound alike when spoken. See http://en.wikipedia.org/wiki/Metaphone#Double_Metaphone for more details.

For example:

`dmetaphone("Smith", value("Last Name"))` return <varies>

`dmetaphone("Smith", "Smythe")` returns true

`dmetaphone("Smith", "Schmidt")` returns true

`dmetaphone("Smith", "Might")` returns false

Also see soundex().
Find a string within another string and return its position.
index(string : string, substring : string) : integer

For example:

`index("New York, NY", "n")` returns 0

`index("New York, NY", ",")` returns 9

`index("New York, NY", "NY")` returns 11

`index("New York, NY", "NV")` returns 0

`index("string", "")` returns <unknown> when the search string is empty

Perform a search of string based on a regular expression, regexp. This function returns 1 (true) if a match is found, and 0 (false) if no match is found or the regular expression is invalid. Process like the Integrator regexp() function with slightly different calling syntax. For more information about regular expressions, see the Microsoft Regular Expressions Quick Reference.
integ_regexp(string : string, regex : string, case_insensitive : boolean) : integer

For example:

`integ_regexp("Joe Smo", "^jo")` returns 0

`integ_regexp("Joe Smo", "^jo", true)` returns 1

Perform a search of string based on a regular expression, regexp. This function returns the portion of the string that matches the specified regular expression. Process like the Integrator regexp_value() function with slightly different calling syntax. For more information about regular expressions, see the Microsoft Regular Expressions Quick Reference.
integ_regexp_value(value : string, regex : string, case_insensitive : boolean) : string

For example:

`integ_regexp_value("Wholesale, Inc. (182)", "([0-9][0-9]*)")` returns (182)

TIP: The third argument is optional—if used, it needs to be true or false.

Return the length of a string.
length(string : string) : integer

For example:

`length("How long?")` returns 9

Compute the Levenshtein distance between two strings.
levenshtein(string1 : string, string2 : string) : integer
This is a similarity measure between two strings. It takes two strings and produces the number of edits that change the first string into the second string. See http://en.wikipedia.org/wiki/Levenshtein_distance.

For example:

`levenshtein("kitten", "sitting")` returns 3

`levenshtein("Quiet Clam", "Quite Calm")` returns 4

Return the lower-case variant of the specified string.
lower(string : string) : string

For example:

`lower("Burlington, MA")` returns burlington, ma

Pad the beginning of the specified string repeatedly with pad_string until the result is length characters long.
lpad(string : string, length : integer, pad_string : string) : string
If pad-string is omitted, spaces are assumed.

For example:

`lpad("foo", 5, "x")` returns xxfoo

`lpad("James", 10, "+-")` returns -+-+-James

`lpad("Long string", 5, "x")` returns Long string

`lpad("bar", 5)` returns   bar (two spaces precede)

Return a string after removing leading characters from the string which are present in trim_characters.
ltrim(string : string, trim_characters : string) : string
If trim_characters are omitted, blank spaces are assumed.

For example:

`ltrim("    test")` returns test

`ltrim("xxxab", "x")` returns ab

`ltrim("*  * **Hello", " *")` returns Hello

`ltrim("xxxab", "xab")` returns <nothing>

Return the specified string with characters that are not valid in filenames replaced by underscores.
map_filename(string : string) : string

For example:

`map_filename("mth**a1")` returns mth__a1

`map_filename("New York, N.Y.")` returns New_York__N.Y.

Trims excess whitespace in a string, leaving only one whitespace character between any non-whitespace characters. This function also trims all whitespace from the start and end of a string.

For example:

`normalize_space("  a       b       ")` returns "a b"
Perform a search of string based on a regular expression, regexp. This function returns 1 (true) if a match is found, and 0 (false) if no match is found or the regular expression is invalid. Process the same as integ_regexp but with full Perl-style regexes. For more information about regular expressions, see the Microsoft Regular Expressions Quick Reference.
regexp(string : string, regex : string, case_insensitive : boolean) : integer

For example:

`regexp("Joe Smo", "^jo")` returns 0

`regexp("Joe Smo", "^jo", true)` returns 1

`regexp(value("Drug Description"), "BB", true)` ignores case when processing the expression

Take a string, match a Perl-style regex to it, and replace the matches with a specified string. For more information about regular expressions, see the Microsoft Regular Expressions Quick Reference.
regexp_subst(string : string, pattern : string, replacement : string, case_insensitive : boolean) : string

For example:

`regexp_subst("foo bar baz", "ba(.)", "ba$1$1")` returns foo barr bazz

TIP: The fourth argument is optional—if used, it needs to be true or false.

Perform a search of string based on a regular expression, regexp. This function returns the portion of the string that matches the specified regular expression. Process the same as integ_regexp_value but with full Perl-style regexes. For more information about regular expressions, see the Microsoft Regular Expressions Quick Reference.
regexp_value(value : string, regex : string, case_insensitive : boolean) : string

For example:

add "Click_Invoice" `regexp_value(value("Transaction Description"),"I# .*.[A-Z][A-Z]*#")` defines a new column for the build.

TIP: The third argument is optional—if used, it needs to be true or false.

Replace every occurrence of a string within another string.
replace(string : string, substring : string, replacement : string) : string

For example:

`replace("We had an bogus time", "bogus", "excellent")` returns We had an excellent time

`replace("A|B|C", "|", "-")` returns A-B-C

`replace("We can merge words", " ", "")` returns Wecanmergewords

Reverse the position of the characters in a string.
reverse(string : string) : string

For example:

`reverse("abc")` returns cba

Find the position of the last occurrence of the specified substring in the specified string.
rindex(string : string, substring : string) : integer

For example:

`rindex("abcabcabc", "ab")` returns 7

`rindex("abcabcabc", "")` returns <unknown> when the search string is empty

Pad the end of the specified string repeatedly with pad_string until the result is length characters long.
rpad(string : string, length : integer, pad_string : string) : string
If pad_string is omitted, spaces are assumed. If the string length is too short, no padding occurs.

For example:

`rpad("foo", 5, "x")` returns fooxx

`rpad("James", 10, "+-")` returns James+-+-+

`rpad("Long string", 5, "x")` returns Long string

`rpad("bar", 5)` returns bar

Reverse substring. Return a string which is the substring of string string starting from the right at character offset start (starting at 1) and having length characters. Both start and length must be integers.
rsubstr(string : string, start : integer, length : integer) : string

For example:

`rsubstr("columnname", 1, 4)` returns name

`rsubstr("columnname", 3, 4)` returns mnna

Return a string after removing trailing characters from the string which are present in trim_characters.
rtrim(string : string, trim_characters : string) : string
If trim_characters are omitted, blank spaces are assumed.

For example:

`rtrim("test    ")` returns test

`rtrim("abxxx", "x")` returns ab

`rtrim("Hello   * -*", " -*")` returns Hello

`rtrim("xxxab", "xab")` returns <nothing>

Parse the string into separate segments based on the set of characters in delimiters.
scan(string : string, index : integer, delimiters : string) : string
If delimiters is omitted, it defaults to spaces.

For example:

`scan("first,second,third", 2, ",")` returns second

`scan("first,second,,fourth", 4, ",")` returns fourth

`scan("foo bar", 4)` returns <nothing>

`scan("foo bar", 1)` returns foo

`scan("foo|bar/baz", 3, "|/")` returns baz

`scan("c:\\\\models\\\\data\\\\sales.mdl", -1, "\\\\")` returns sales.mdl

`scan("c:\\\\models\\\\data\\\\sales.mdl", -2, "\\\\")` returns data

`scan("c:\\\\models\\\\data\\\\sales.mdl", -4, "\\\\")` returns c:

`scan("c:\\\\models\\\\data\\\\sales.mdl", -5, "\\\\")` returns null (0)

`scan("c:/models/data/sales.mdl", -2, "/")` returns data

`scan("data/sales.mdl", -2, "/")` returns data

`scan("cbases/sales.cbase", -1, "/")` returns sales.cbase

`scan(Delivery Time, 1, ".")` returns just the MM-DD-YYYY hh:mm:ss portions of a MM-DD-YYYY hh:mm:ss.SSS string with milliseconds

Perform phonetic string mapping.
soundex(string : string) : string
Soundex is a phonetic mapping algorithm that tries to produce the same string for inputs that sound alike. The output string itself is meaningless, but you can use it in comparisons like `soundex(value("A")) = soundex("Smith")`. See http://en.wikipedia.org/wiki/Soundex for the full explanation.

For example:

`soundex("Robert") = soundex("Rupert")` returns true

`soundex("Robert") = soundex("Robin")` returns false

Also see dmetaphone().
Determine if one string starts with another.
startswith(string : string, prefix : string) : boolean

For example:

`startswith("Customer", "Cust")` returns 1

`startswith("Provider", "pro")` returns 0

Return a string which is the substring of string string starting from the left at character offset start (starting at 1) and having length characters. Both start and length must be integers.
substr(string : string, start : integer, length : integer) : string

For example:

`substr("this is a test", 2, 5)` returns "his i"

`substr("this is a test", 3)` returns "is is a test"

NOTE: Avoid using substr() to manipulate date values—it is much more efficient to use date and period functions.
Return a count of the tokens in the string using the delimiters to parse the string. If no delimiter is specified, blank is assumed.
token_count(string : string, delimiters : string) : integer

For example:

`token_count("first, second, third", ",")` returns 3

`token_count("foo|bar/baz", "|/")` returns 3

`token_count("foo bar")` returns 2

`token_count("/path/to/file.txt", "/")` returns 4

For each character in string that occurs in the from_characters string, replace it with the character in the to_characters string corresponding to the first occurrence of X in the from string.
translate(string : string, from_characters : string, to_characters : string) : string

For example;

`translate("Mary had a little lamb", " ", "x")` returns Maryxhadxaxlittlexlamb

`translate("able", "abcde", "bcdea")` returns bcla

`translate("Dimensional Insight", "DI", "d")` returns dimensional Insight

`translate(value("ClientID"), "0123456789", "abcdefghij")` replaces each number in Client ID with a letter.

Return the upper-case variant of the specified string.
upper(string : string) : string

For example:

`upper("ibm")` returns IBM

`upper("jon brown")` returns JON BROWN

See also:

Mentioned in: