Simple Find and Replace
JMP's scripting language, JSL, can store numbers, strings, and other objects in variables. A string assignment might look like this:
place = "Cary, North Carolina, USA";the variable place now holds the string. Here's a set of functions for working with strings in JSL. Place will keep that same string in all the examples. Regex and Pattern Matching are covered in later posts.
Concatenation - put two strings together, end-to-end. Or more than two...
sentence = "My home is " || place || ".";"My home is Cary, North Carolina, USA."
Length - how many characters are in the string?
Write( place, " ", Length( place ) );Cary, North Carolina, USA 25
Contains - what part of the string contains this text?
positionA = Contains( place, "," );
positionB = Contains( place, ",", 1 + positionA );
Show( positionA, positionB );
positionA = 5;
positionB = 21;
Notice how positionB is found starting one character after positionA. Otherwise the first comma at positionA would be found again. Contains returns a position of 0 if the search fails, making if( contains(...), ... ) work nicely.
Substr - part of the string, starting at a position, for a length.
state = Substr( place, 1 + positionA, positionB - (1 + positionA) );" North Carolina"
Notice the space after the first comma has been captured too. If you leave out the length argument, substr goes to the end. There is also a rule for a negative starting position:
Substr( place, -3, 2 );"US"
-3 means start 3 characters from the end.
Trim - remove those pesky blanks
state = trim( state, "left" ); // or "right" or "both""North Carolina"
Left - some number of characters from the left (front) end of the string
Left( place, positionA - 1 );"Cary"
positionA is 5, the position of the first comma; "Cary" is the 4 characters before the comma. There is a third argument for filler text:
Left( "abc", 15, "@#$*" );
"abc@#$*@#$*@#$*"
Notice how enough of the filler text is added to provide the requested 15 characters from the left of "abc". Space might be a better filler choice. Without the filler the result is just "abc".
Right - some number of character from the right (back) end of the string
Right( place, 3 ); // like Left, there is a fill option"USA"
Use Substr instead of right if you have a position
Substr( place, positionB + 2 ); // +2 goes past the comma and the space"USA"
Substr doesn't require a third value for the length; it will go to the end.
You could use Right with a position and the Length function, but by the time you've explained about subtracting a position from a length, it will be more complicated.
Munger - jack of all trades...
Munger munges together search and replace. It isn't as clever or well known as Regex, but is easier to understand, once you get the hang of it. No one else will be able to figure out what your JSL is doing without a manual, so you might want to skip it.
result = Munger( source, startPosition, textOrLength, optionalReplacementText ); Here's a table that attempts to explain the various combinations of textOrLength and optionalReplacementText. All the examples use 3 for the starting position, skipping over the initial "ab". Notice the fourth example finding the second "b".
| no replacement | replacement text = "$*?" | |
| Munger("abcabc", 3, 2, ? ) | "ca" | "ab$*?bc" |
| Munger("abcabc", 3, 0, ?) | "" | "ab$*?cabc" |
| Munger("abcabc", 3, 99, ?) | "cabc" | "ab$*?" |
| Munger("abcabc", 3, "b", ?) | 5 | "abca$*?c" |
| Munger("abcabc", 3, "Q", ?) | 0 | "abcabc" |
| Munger("abcabc", 3, "", ?) | 0 | "abcabc" |
If you stare at it long enough all the results are useful in a self-consistent way. If there is no text to find, just a length, Munger returns the substring. If there is replacement text, it returns the original with the substring replaced.But if there is text to find there is no reason to return the found text, so if there is no replacement text you'll get the position of the found text. If there is replacement text, you'll get the original string with the substring replaced.The odd case is the last line, which might or might not meet your expectations for finding an empty string. Don't use negative positions and lengths either. They won't add any clarity here and don't work like substr().
Example
Data table column formulas can compute a value from another column. Here's a "last name, first name" example that assumes the comma is a unique delimiter. This example works for the "onlyname" case because of the way Left interprets any out-of-range length as everything. Look for the Regex post, you might like it better.
edit 30Sep2017: repair formatting