Extracting the domain name in SQL Server is sometimes necessary for email address management or analysis, URLs, or even Active Directory data. SQL Server offers a set of string functions that can help us to obtain the domain name easily from such sources. Let’s explore different ways of extracting domain names using SQL queries.
Table of Contents
Understanding the Need
What Is Domain Name?
A domain name is an address for any resource on the internet, like the part of an email after the @ symbol (example.com) or the root of a URL (https://example.com).
Common Use Cases in SQL Server
- Extracting domain names for email verifications.
- Analyzing traffic according to URLs.
- Maintaining Active Directory records.
Extracting Domain Name from Email Addresses
Using CHARINDEX & SUBSTRING Functions
The CHARINDEX function finds the position of characters such as @ while the SUBSTRING function pulls out the domain from that particular position.
Examples
DECLARE @Email NVARCHAR(100) = '[email protected]';
SELECT SUBSTRING(@Email, CHARINDEX('@', @Email) + 1, LEN(@Email)) AS DomainName;
Output
example.com
Explanation
- CHARINDEX(‘@’, @Email) tries finding the position of @.
- SUBSTRING(@Email, CHARINDEX(‘@’, @Email) + 1, LEN(@Email)) extracts the text starting after the @.
Extracting Domain Name from URLs
Using CHARINDEX, PATINDEX, & SUBSTRING
To grab the domain from a URL, locate the start of the domain after // and identify where it ends using string functions.
Example
DECLARE @URL NVARCHAR(100) = 'https://www.example.com/page';
SELECT SUBSTRING(
@URL,
CHARINDEX('//', @URL) + 2,
CHARINDEX('/', @URL, CHARINDEX('//', @URL) + 2) - CHARINDEX('//', @URL) - 2
) AS DomainName;
Output:
www.example.com
Handling Complex URLs
Use PATINDEX to find patterns dynamically for URLs with or without www.
DECLARE @URL NVARCHAR(100) = 'https://example.com/page';
SELECT SUBSTRING(
@URL,
CHARINDEX('//', @URL) + 2,
CHARINDEX('/', @URL + '/', CHARINDEX('//', @URL) + 2) - CHARINDEX('//', @URL) - 2
) AS DomainName;
Extracting Domain Name from Active Directory Data
In Active Directory, entries may refer to domains either in UserPrincipalName or DistinguishedName.
Example
DECLARE @UPN NVARCHAR(100) = '[email protected]';
SELECT SUBSTRING(@UPN, CHARINDEX('@', @UPN) + 1, LEN(@UPN)) AS DomainName;
Using a User-Defined Function (UDF)
For reusable logic, you can create a UDF to obtain the domain name.
Creating the UDF:
CREATE FUNCTION GetDomainName (@Input NVARCHAR(200), @Delimiter NVARCHAR(1))
RETURNS NVARCHAR(100)
AS
BEGIN
RETURN SUBSTRING(@Input, CHARINDEX(@Delimiter, @Input) + 1, LEN(@Input));
END;
Using the UDF:
SELECT dbo.GetDomainName('[email protected]', '@') AS DomainFromEmail;
SELECT dbo.GetDomainName('https://example.com/page', '//') AS DomainFromURL;
Conclusion
Domain name extraction in SQL Server has become relatively easy with functions like CHARINDEX, SUBSTRING, and PATINDEX. For the complex cases or if you would be doing it more frequently, you could simply create a UDF here to simplify the operation and enhance reusability. The above-mentioned techniques could also be adapted to handle emails, URLs, or other data sources.
FAQs
Can these queries handle invalid email addresses?
No, these methods assume valid input formats. You can add additional validation to handle incorrect data In these methods, we must have a valid input format for the emails. If you want to handle the incorrectness in the data.
How do I extract subdomains from URLs?
Modify the logic to include www or other subdomains by adjusting the CHARINDEX or PATINDEX position calculations. You can do the same by modifying the logic to www or any other subdomain by adjusting the CHARINDEX or PATINDEX position calculations.
What if the URL does not contain //?
You can add a conditional check to handle cases where http:// or https:// is missing.
Is there a way to get the domain name in SQL Server Management Studio (SSMS)?
Yes, you can run the above queries in SSMS, ensuring you use correct sample data.
Does SQL Server provide built-in functions for domain extraction?
No, SQL Server does not have built-in domain extraction functions. You must use string manipulation functions or create custom UDFs.