📌SQL Server String Functions — SUBSTRING, CHARINDEX, LEN, and REPLICATE (With Real Examples)

Working with strings in SQL Server is extremely common — especially when dealing with names, emails, or masked data.
In this article, we will look at how to use the core SQL Server string functions:

• 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-world scenarios.

🎯 Scenario

Suppose we want to store or display masked email addresses.
For example:

[email protected]  →  wo*****@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 = 'Nishant';
SET @lastname  = 'Raj';
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

wo

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

FirstName	LastName	MaskedEmail
Nishant	Raj	wo*****@gmail.com

📎 Why This Example Is Useful

• Helps you understand how SUBSTRING and CHARINDEX work together

• Demonstrates how SQL Server handles string masking

• Shows how SQL functions behave when length exceeds the actual string

• Good example to teach beginners OR interview candidates

📌 Summary

Function	Purpose
SUBSTRING()	Extracts part of a string
CHARINDEX()	Finds position of a character
LEN()	Gets string length
REPLICATE()	Repeats characters (used for masking)

Combining these, we can easily build secure and readable outputs like masked email addresses.