Bits of .NET

The Challenge: You have a Users table with thousands of email addresses, and you need to generate a report that separates the Username from the Domain Name. Doing this manually is impossible, and using complex RegEx in SQL can be overkill.

The Solution: By combining three core SQL functions—SUBSTRING, CHARINDEX, and LEN—you can perform surgical strikes on strings to extract exactly what you need.

🚀 The “Clean Code” Script

This script dynamically calculates the position of the @ symbol and slices the string into two distinct parts. Copy and run this in your SSMS to see the magic:

DECLARE @Email VARCHAR(100) = 'info@ercanopak.com';

SELECT 
    -- 1. Extract everything BEFORE the '@'
    SUBSTRING(@Email, 1, CHARINDEX('@', @Email) - 1) AS [User Name],
    
    -- 2. Extract everything AFTER the '@'
    SUBSTRING(@Email, CHARINDEX('@', @Email) + 1, LEN(@Email)) AS [Domain Name];

🔍 How the Logic Works (The “Pro” Breakdown)

To truly master SQL, you need to understand why this works. Here is the mechanical breakdown of the functions used:

• CHARINDEX('@', @Email): This is our “GPS”. It finds the exact numerical position of the @ character.
• The -1 and +1 Logic:
  • We use -1 for the Username because we want to stop just before the @.
  • We use +1 for the Domain because we want to start just after the @.
• LEN(@Email): This tells the SUBSTRING function to keep reading until the very end of the string, ensuring no domain (no matter how long) is cut off.

SELECT 
    CASE WHEN CHARINDEX('@', Email) > 0 
         THEN SUBSTRING(Email, 1, CHARINDEX('@', Email) - 1) 
         ELSE 'Invalid Email' END AS CleanUsername
FROM Users;

Summary

String manipulation is a vital skill for data cleaning and ETL processes. Mastering these three functions allows you to handle not just emails, but URLs, file paths, and any delimited data directly in SQL.