Extract Email Local Part

Extract email username using the text:substring() function with text:positionOf()

Query

flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 1[\Add Field/] result{{Result Set}} repo --> 1 1 --> result
logscale
text:substring(email, end=text:positionOf(email, character="@"), as=localPart)

Introduction

The text:substring() function can be used with text:positionOf() to extract portions of text based on the position of specific characters within a string. The text:positionOf() function helps identify these positions by locating characters or substrings.

In this example, the text:substring() function is used together with text:positionOf() to extract the local part of email addresses. The text:positionOf() function returns the location of a character or substring in a string, in this case, the position of the @ character.

Example incoming data might look like this:

@timestampemail
2025-08-06T10:15:00.000Zjohn.doe@example.com
2025-08-06T10:15:01.000Zjane.smith@company.org
2025-08-06T10:15:02.000Zsupport@service.net
2025-08-06T10:15:03.000Zuser123@domain.com

Step-by-Step

  1. Starting with the source repository events.

  2. flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 1[\Add Field/] result{{Result Set}} repo --> 1 1 --> result style 1 fill:#ff0000,stroke-width:4px,stroke:#000;
    logscale
    text:substring(email, end=text:positionOf(email, character="@"), as=localPart)

    Extracts the local part (the portion before the domain) of an email address using two nested functions:

    • The text:positionOf() function finds the position of the @ character in the email field using the character parameter.

    • The text:substring() function extracts all characters from the start of the string up to (but not including) the @ position (defined by the end parameter), and returns the result in a new field named localPart.

  3. Event Result set.

Summary and Results

The query is used to extract the username portion of email addresses by dynamically finding the @ symbol position and extracting everything before it.

This query is useful, for example, to analyze email patterns, group by usernames, or standardize email processing while keeping only the local part.

Sample output from the incoming example data:

emaillocalPart
john.doe@example.comjohn.doe
"jane.smith@company.org,"jane.smith" 
support@service.netsupport
user123@domain.comuser123

Note that the text:substring() function's end parameter is exclusive, ensuring the @ symbol is not included in the extracted local part.