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 |
4. Pattern to match the last 3 digits of a country calling code.
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:
Centralize Knowledge. Transform Learning.
All-in-one knowledge management and training platform for your employees and customers.
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 ...Target Matching
Target matching happens before the data is exported to the destination. Target matching is a useful feature in DataPrep that prevents export failures caused due to errors from the data model mismatch. Using target matching, you can set the required ...Import data from Google Drive
Zoho DataPrep supports importing data from Google Drive, a file storage and synchronization service developed by Google. Google Drive allows users to store files in the cloud, synchronize files across devices, and share files. To import data from ...Import data from Box
Zoho DataPrep supports importing data from Box, a cloud storage service that allows users to store, collaborate, and manage files securely in an online folder system accessible from any device. Note: Box Cloud storage is supported across all DCs ...Import data from Amazon S3
Zoho DataPrep supports importing data from Amazon S3 cloud storage service. Amazon Simple Storage Service, also known as Amazon S3 from AWS provides object storage through a web service interface. Important Prior to connecting your Amazon S3 data ...
New to Zoho LandingPage?
Zoho LandingPage Resources