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.
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
For example (using ASCII):
`chr(65) returns A
`chr(32) returns space
`chr(92) returns \
For example:
`concat("ab", "c", " ", "defg")` returns abc defg
concat("Smith", ", ", "John") returns Smith, John
For example:
`damerau("kitten", "sitting")` returns 3
`damerau("Quiet Clam", "Quite Calm")` returns 2
For example:
`dmetaphone("Smith", value("Last Name"))` return <varies>
`dmetaphone("Smith", "Smythe")` returns true
`dmetaphone("Smith", "Schmidt")` returns true
`dmetaphone("Smith", "Might")` returns false
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 0 when the search string is empty
For example:
`integ_regexp("Joe Smo", "^jo")` returns 0
`integ_regexp("Joe Smo", "^jo", true)` returns 1
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.
For example:
`length("How long?")` returns 9
For example:
`levenshtein("kitten", "sitting")` returns 3
`levenshtein("Quiet Clam", "Quite Calm")` returns 4
For example:
`lower("Burlington, MA")` returns burlington, ma
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)
For example:
`ltrim(" test")` returns test
`ltrim("xxxab", "x")` returns ab
`ltrim("* * **Hello", " *")` returns Hello
`ltrim("xxxab", "xab")` returns <nothing>
For example:
`map_filename("mth**a1")` returns mth__a1
`map_filename("New York, N.Y.")` returns New_York__N.Y.
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
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.
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.
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
For example:
`reverse("abc")` returns cba
For example:
`rindex("abcabcabc", "ab")` returns 7
`rindex("abcabcabc", "")` returns 0 when the search string is empty
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
For example:
`rsubstr("columnname", 1, 4)` returns name
`rsubstr("columnname", 3, 4)` returns mnna
For example:
`rtrim("test ")` returns test
`rtrim("abxxx", "x")` returns ab
`rtrim("Hello * -*", " -*")` returns Hello
`rtrim("xxxab", "xab")` returns <nothing>
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
For example:
`soundex("Robert") = soundex("Rupert")` returns true
`soundex("Robert") = soundex("Robin")` returns false
For example:
`startswith("Customer", "Cust")` returns 1
`startswith("Provider", "pro")` returns 0
For example:
`substr("this is a test", 2, 5)` returns "his i"
`substr("this is a test", 3)` returns "is is a test"
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 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.
For example:
`upper("ibm")` returns IBM
`upper("jon brown")` returns JON BROWN
See also: