Text and pattern matching
Zoho DataPrep supports the following text and pattern matching types.
Text literals: Text literals are used to match the exact specified text in your data.
Regular expressions: Regex are used to match data based on the expression provided. For example, the regex ^\d+ is used to match any number of digits in the input data from the staring position.
Patterns: Pattern tokens provide a simpler and more readable alternative to regular expressions.
The following section describes how pattern matching works in Zoho DataPrep.
Patterns
The following table contains the list of tokens and the syntax for each pattern supported in DataPrep.
Character patterns
These tokens apply to one or more characters part of the text data type.
|
Pattern
|
Description
|
|
{alpha}
|
Set of all alphabets [A-Za-z]
|
|
{alpha_numeric}
|
Set of all alphabets and numbers [A-Za-z0-9]
|
|
{lower}
|
Set of all lower characters [a-z]
|
|
{upper}
|
Set of all upper characters [A-Z]
|
|
{digit}
|
Set of all integers [0-9]
|
|
{number}
|
Set of all integers and decimal numbers
|
|
{special_character}
|
Set of all special characters [e.g. -/,*&^%#@! etc.]
|
|
{white_space}
|
A white space character [' ']
|
|
{any}
|
Set of all characters
|
|
{other}
|
Set of all non-ASCII characters
|
|
{'constant'}
|
Matches the given text enclosed inside single quotes or double quotes
|
|
{word}
|
Set of all alphabets, numbers and underscore i.e. {alpha_numeric} (including underscore)
|
|
{username}
|
Characters prefixed with @
|
|
{hashtag}
|
Characters prefixed with #
|
Position patterns
These tokens apply to the positions relative to the text data type.
|
Pattern
|
Description
|
|
{start}
|
Start of the line
|
|
{end}
|
End of the line
|
Pattern construction
The following rules apply while constructing patterns for matching data.
- Enclose a pattern token within '{ }'.
E.g. {alpha} - Append a '*' after the token to match zero or more number of occurrences.
E.g. {alpha}* - Append a '+' after the token to match one or more number of occurrences.
E.g. {alpha}+ - Append a number after the token to match the exact number of occurrence.
E.g. {alpha}{3} - Enclose a lower and upper bound constraint within '{ }' after the token to match within a specified range.
E.g. {alpha}{2,5} - Enclose constants within ' ' to form a token.
E.g. {'Bob'}
You can add tokens one after the other to construct a full length pattern with multiple matching conditions. If you wish to match either of the two patterns, use the logical OR condition using ' | ' in between the two patterns.
For e.g., {'Bob'} | {'Robert'}
Pattern examples
1. Pattern to match the first word in a text.
|
Input data |
DataPrep supports pattern matching. |
|
Pattern |
{start}{alpha}+ |
|
Matched data |
DataPrep |
2. Pattern to match the last 3 digits of a country calling code.
|
Input data |
+1 340 |
|
Pattern |
{digit}{3}{end} |
|
Matched data |
340 |
3. Pattern to match a credit card number:
|
Input data |
1234-1234-1234-1234 |
|
Pattern |
{start}{digit}{4}{special_character}{digit}{4}{special_character}{digit}{4}{special_character}{digit}{4}{end} |
|
Matched data |
1234-1234-1234-1234 |
Note: You can include the {start} and {end} tokens to match the data only when the pattern is found across the entire cell value.
SEE ALSO
Access your files securely from anywhere
Zoho CRM Training Programs
Learn how to use the best tools for sales force automation and better customer engagement from Zoho's implementation specialists.
Zoho DataPrep Personalized Demo
If you'd like a personalized walk-through of our data preparation tool, please request a demo and we'll be happy to show you how to get the best out of Zoho DataPrep.
- Zoho Workerly Home
- Forums
- Connect With Us:
- Zoho Recruit Home
- Forums
- Connect With Us:
New to Zoho Writer?
Create. Review. Publish.
New to Zoho CRM Plus?
Deliver unforgettable customer experiences
New to Zoho CRM Plus?
Deliver unforgettable customer experiences
New to Zoho Marketing Plus?
Everything you need to run your marketing
New to Zoho Marketing Plus?
Everything you need to run your marketing
You are currently viewing the help pages of Qntrl’s earlier version. Click here to view our latest version—Qntrl 3.0's help articles.
New to Zoho Survey?
Zoho Sheet Resources
Zoho Forms Resources
New to Zoho Sign?
Zoho Sign Resources
Sign, Paperless!
New to Zoho TeamInbox?
Zoho TeamInbox Resources
New to Zoho ZeptoMail?
Design. Discuss. Deliver.
New to Zoho Workerly?
New to Zoho Recruit?
New to Zoho CRM?
New to Zoho Projects?
New to Zoho Sprints?
New to Zoho Assist?
New to Bigin?
Related Articles
Extract from text
Zoho DataPrep offers options to identify and extract a subset of the data from a column. You can extract very specific portions of the column data using the extract transform. For example, your column contains a mixture of letters and numbers, but ...Find and Replace
Find and replace values in your column using the replace transform. You can find the value to replace by matching the value using different options available and replace it with your input. To find and replace column data 1. Right-click the column ...Create buckets - Text
You can choose to implement bucketing in a text column using conditions. Consider a situation where you need to assign people to specific teams. You can implement the logic of adding values to a bucket, which in this case is the team name. Name Team ...Count
DataPrep allows you to count the occurrence of data in a column based on the condition given. You can count the values that match your selection based on the different patterns that fit your selection. To count the desired value in a column 1. ...Split
DataPrep allows you to split the columns based on the given input. A column can be split into multiple columns using a delimiter based on the chosen split option. You can divide a column into multiple columns using the Split transform. To split a ...
New to Zoho LandingPage?
Zoho LandingPage Resources