Twenty20 Systems Joins Mulesoft Partner Program
June 18, 2022
Twenty20 Systems Announces Partnership with dbt Labs™
February 10, 2023
String Manipulation in Workato
Working on projects that deal with large amounts of data comes with their own set of challenges. One of these challenges is figuring out how to transform data from one format to another. Oftentimes requirements call for data to be transformed from upper case to lower case, combining data like first name and last name, or extracting a small subset of data from a large chunk of text and that’s where String Manipulation shines.
This is the first of many blog posts about String Manipulation.
One of our clients is a travel company that wanted to extract a small chunk of data from a larger volume of information. The use case is for a travel chatbot to retrieve a trip's description based on the user-entered "From" and "To" locations. The chatbot will then simply extract the trip's description from the travel data. We will use that use case as an example in this post to showcase the power of string manipulation in Workato.
Want to transform your data?
What is String Manipulation and why do we need it in Workato?
String Manipulation in Workato helps streamline a recipe's steps for extracting fields from an array of values and modifying it to produce the desired result. Workato’s inbuilt string manipulation function uses Ruby code in the background which makes it an easy-to-use no-code/low-code platform for developers and non-developers alike. Workato has exposed these functions in a simple way for anyone to use and transform data.
There are various types of String Manipulation functions one can use:
include?, exclude?, match?, parameterize, lstrip, rstrip, scrub, ljust, rjust, reverse, sub, length, slice,
and many many more.
In this post, we will specifically look at .gsub() and pluck() functions and how we used them in our customer use case effectively.
- .gsub() allows you to substitute the entire original string or a sub-string with the desired replacement.
- The Syntax for gsub is:
inputString.gsub(find, replace)
Where
- find - The string to search for
- replace - The substitute string
- pluck() allows you to retrieve the desired columns of data that have been specified.
Formula Mode:
- String manipulation in Workato is performed using formulas in the formula mode.
- Click the formula button in the top-right corner of the input field to go from text mode to formula mode. To show that you are in formula mode, the background colour is changed by doing this. The left-hand icon will likewise change to the fx icon.
Formula mode is displayed with the fx symbol
- The string type input field's icon changes from String type to formulas type when the input field is toggled into formula mode.
- When in formula mode, the formula editor offers a selection of formulae from the allowed list that can be used to convert or manipulate data, along with more guidance on how to do so.
Filtering Formulae
The editor displays a list of formulas that can be used with strings when a data-pill of the string type is dropped into an input field while in formula mode.
Formula editor showing the list of string formulas
Now let's look at how gsub and pluck are used in our use case.
Input:
The input data contains multiple description fields(along with a label). We need to extract each of these description fields quickly and send it to the chatbot in an order.
[
{
"Label": "Moving to New York",
"Description": "Traveling from London to New York to see Disney World.",
"SectionList":
{
"SectionName":
[
"Group": "Five friends travel to New York together"
]
}
},
{
"Label ": "Moving to Canada",
"Description": " Traveling from New York to Canada to see Niagara Falls and to have deilictable foods.",
"SectionList":
{
"SectionName":
[
"Group”: “Six friends travel to France together "
]
}
},
{
"Label ": "Moving to Bermuda",
"Description": " Traveling from Canada to Bermuda to see Pink sand Beaches.",
"SectionList":
{
"SectionName":
[
"Group”: “Seven friends travel to Bermuda together "
]
}
},
{
"Label ": "Moving to Cuba",
"Description": " Traveling from Bermuda to Cuba to see Fascinating Architecture and to have delictable foods and drinks.",
"SectionList":
{
"SectionName":
[
"Group”: “Six friends travel to Bermuda together "
]
}
},
{
"Label ": "Moving to Newyork",
"Description": " Traveling from Cuba to New York to see Fascinating Architecture and to have Manhattan clam chowder.",
"SectionList":
{
"SectionName":
[
"Group”: “Five friends travel to Bermuda together "
]
}
}
]
Desired Output:
The desired output should be a list of descriptions prefixed by a hyphen:
Description:
-Traveling from London to New York to see Disney World.
-Traveling from New York to Canada to see Niagara Falls and to have delectable foods.
-Traveling from Canada to Bermuda to see Pink sand Beaches.
-Traveling from Bermuda to Cuba to see Fascinating Architecture and to have delectable foods and drinks.
-Traveling from Cuba to New York to see Fascinating Architecture and to have Manhattan clam chowder.
Here are the steps performed to extract the descriptions:
The data may contain more than one Description label, and we must remove all potential brackets (i.e. {, ], }, [, ) To distinguish between the descriptions, each description field must begin with a hyphen, and the following value should appear on the following line.
Formula used:
Data. pluck('Description').to_s. gsub (`"`,`-`). gsub (`[`,``). gsub (`]`,``). gsub(`-,`,`n`). gsub (`.-`,`.`)
Explanation
- Before using .gsub() operation, we must apply the pluck operation when the incoming data contains an array of values, and we only require one or more than one field from it.
Description:
[
"Traveling from London to New York to see Disney World.","Traveling from New York to Canada to see Niagara Falls and to have deilictable foods.","Traveling from Canada to Bermuda to see Pink sand Beaches.","Traveling from Bermuda to Cuba to see Fascinating Architecture and to have delictable foods and drinks.","Traveling from Cuba to Newyork to see Fascinating Architecture and to have Manhattan clam chowder."
]
- Convert the incoming data to a string to apply string formulae.
- Use gsub to replace all double quotes to hyphens.
gsub (`"`,`-`):
- The result will be the descriptions contained within square brackets.
Description:
“[
-Traveling from London to New York to see Disney World. -,-Traveling from New York to Canada to see Niagara Falls and to have deilictable foods.-,-Traveling from Canada to Bermuda to see Pink sand Beaches.-,-Traveling from Bermuda to Cuba to see Fascinating Architecture and to have delictable foods and drinks.-,-Traveling from Cuba to Newyork to see Fascinating Architecture and to have Manhattan clam chowder.-
]”
- The next step is to replace all the Square Brackets (open and close) with empty string
gsub (`[`,``) and gsub (`]`,``):
- (i.e., ``)
Description:
"-Traveling from London to New York to see Disney World. -,-Traveling from New York to Canada to see Niagara Falls and to have deilictable foods.-,-Traveling from Canada to Bermuda to see Pink sand Beaches.-,-Traveling from Bermuda to Cuba to see Fascinating Architecture and to have delictable foods and drinks.-,-Traveling from Cuba to Newyork to see Fascinating Architecture and to have Manhattan clam chowder.-"
We will then replace all the hyphen and comma pair with a new line(n) i.e., it will push the next description to next line.
gsub(`-,`,`n`):Output:
" -Traveling from London to New York to see Disney World.
-Traveling from New York to Canada to see Niagara Falls and to have deilictable foods.
-Traveling from Canada to Bermuda to see Pink sand Beaches.
-Traveling from Bermuda to Cuba to see Fascinating Architecture and to have delictable foods and drinks.
-Traveling from Cuba to Newyork to see Fascinating Architecture and to have Manhattan clam chowder.-"
The final step is to replace all the period and hyphen pairs with just a period.
gsub (`.-`,`.`)
Final result
Description:
" -Traveling from London to New York to see Disney World.
-Traveling from New York to Canada to see Niagara Falls and to have deilictable foods.
-Traveling from Canada to Bermuda to see Pink sand Beaches.
-Traveling from Bermuda to Cuba to see Fascinating Architecture and to have delictable foods and drinks.
-Traveling from Cuba to Newyork to see Fascinating Architecture and to have Manhattan clam chowder."
Conclusion
And there you have it! The stated string manipulation technique will aid in streamlining the recipe's steps for extracting the desired fields value from an array of values and modifying it to produce the desired result.
We hope this blog provided an in-depth description about String Manipulation in Workato using .gsub(). If you have any questions or feedback related to this article, please feel free to reach out to us using the “Contact Us” page.
Want to discuss more about String Manipulation in Workato? We’re always open to chat!
About the Author
Shivakumara Avadhani
Shiva is our Senior Software Engineer based out of our Bangalore office. His interests include playing cricket, traveling, and learning different languages. During his free time, he can be found solving math puzzles, mentoring, reading technical blogs, and cooking! With a never give up attitude and experience in various technology platforms, he's the Bangalore team's primary problem solver.