📌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

This tells us the @ symbol starts at character 4.

3️⃣ LEN()

Returns the total length of a string.

LEN('[email protected]')

Output

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.