In SQL Server, working with strings is very common, particularly when working with names, emails, or anything that has been concealed.
We'll examine how to use the fundamental SQL Server string functions in this article:

• SUBSTRING()
• CHARINDEX()
• LEN()
• REPLICATE()

and combine them to mask an email address.

This is a perfect example to understand how these functions behave in real-hfrld scenarios.

Scenario
Suppose we want to store or display masked email addresses.
For example:
[email protected]  →  hf*****@gmail.com

We want to:
Keep the first 2 characters
Replace the next few characters with *

Keep the domain part (@gmail.com)
Let’s see how SQL handles this.

Sample SQL Code
DECLARE @firstname VARCHAR(100),
        @lastname  VARCHAR(100),
        @email     VARCHAR(100);

SET @firstname = 'Peter';
SET @lastname  = 'Scott';
SET @email     = '[email protected]';

SELECT
    @firstname AS FirstName,
    @lastname AS LastName,
    SUBSTRING(@email, 1, 2)                     -- First 2 characters
        + REPLICATE('*', 5)                     -- Mask with 5 stars
        + SUBSTRING(@email, CHARINDEX('@', @email),
          LEN(@email) - CHARINDEX('@', @email) + 1)   -- Extract domain
        AS MaskedEmail;

Understanding the Functions
1. SUBSTRING()
Extracts part of a string.

Example
SUBSTRING('[email protected]', 1, 2)

Output
hf

2. CHARINDEX()
Finds the position of a character inside a string.

CHARINDEX('@', '[email protected]')

Output
4

This tells us the @ symbol starts at character 4.

3. LEN()
Returns the total length of a string.

LEN('[email protected]')

Output
13

4. REPLICATE()
Repeats a character or string multiple times.

REPLICATE('*', 5)

Output
*****

Extracting the Domain
To extract everything from @ onward:

SUBSTRING(@email, CHARINDEX('@', @email), LEN(@email))

Output
@gmail.com

We start at the @, so no need for +1.

Final Output