How to split an email address into its parts

Question

Mail data:

Mailid
-------------------------------
[email protected]
[email protected]

Expected output

Name                 DomainName  Extension
-------------------  ----------  ---------
venkattaramanan1985  gmail       com
madanraj             gmail       com

Answer 1

It has a simple one line solution, Assume email address is [email protected], below query will return parts as mentioned:

SELECT 
--admin
LEFT(emailAddres, CHARINDEX('@', emailAddres) - 1) AS accountName  

--system.org
RIGHT(emailAddres, LEN(emailAddres) - CHARINDEX('@', emailAddres)) AS domainWithExtension

--system
LEFT(RIGHT(emailAddres, LEN(emailAddres) - CHARINDEX('@', emailAddres)), CHARINDEX('.', RIGHT(emailAddres, LEN(emailAddres) - CHARINDEX('@', emailAddres))) - 1) AS domain

--org
RIGHT(RIGHT(emailAddres, LEN(emailAddres) - CHARINDEX('@', emailAddres)), LEN(RIGHT(emailAddres, LEN(emailAddres) - CHARINDEX('@', emailAddres))) - CHARINDEX('.', RIGHT(emailAddres, LEN(emailAddres) - CHARINDEX('@', emailAddres)))) AS extension

Hope this help.

Answer 2

First of all, why use the SQL server for that?

I recommend to use the client for the string manipulation and just let the SQL server return the data. That should be the only job for the SQL server for your case.

If you really have to use the SQL server for that you may want to read how to split strings in SQL beforehand. (for performance comparison read here)

For the following proposed solution you need a table-value-function to split a passed string with a specified delimiter which returns the ordered substrings. I modified the Common Table Expression taken from here to return the ordering as well.

CREATE FUNCTION dbo.SplitStrings_CTE
(
   @List       NVARCHAR(MAX),
   @Delimiter  NVARCHAR(255)
)
RETURNS @Items TABLE ([Order] INT IDENTITY(1,1) NOT NULL, [Item] NVARCHAR(4000))
WITH SCHEMABINDING
AS
BEGIN
    --same as the original here, the ordering is inserted automatically by IDENTITY
    (...)
END

Now I added a function to split an email string re-using the function above:

CREATE FUNCTION dbo.SplitEmail
(
   @email NVARCHAR(254)
)
RETURNS @splitted TABLE (
    [Email] NVARCHAR(254),
    [Name] NVARCHAR(254),
    [DomainName] NVARCHAR(254),
    [Extension] NVARCHAR(254)
)
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @name NVARCHAR(254), @domain NVARCHAR(254), @ext NVARCHAR(254)
    --init the email parts with defaults
    SELECT @name = ISNULL(@email, ''), @domain = '', @ext = '';
    --we only want to split @email if at least one '@' is found in it
    IF (@email IS NOT NULL AND LEN(@email) <> 0 AND CHARINDEX('@', @email) <> 0) BEGIN
        --take the last occuring substring from @email as the @domain
        SELECT TOP 1 @domain = [Item]
        FROM [dbo].[SplitStrings_CTE](@name, '@')
        ORDER BY [Order] DESC
        --@email without @domain + '@' is the @name
        SET @name = LEFT(@name, LEN(@name) - LEN(@domain) - 1)
        --we only want to split @domain if at least one '.' is found in it
        IF (CHARINDEX('.', @domain) <> 0) BEGIN
            --take the last occuring substring from @domain as the @ext
            SELECT TOP 1 @ext = [Item]
            FROM [dbo].[SplitStrings_CTE](@domain, '.')
            ORDER BY [Order] DESC
            --split the @ext from the @domain
            SET @domain = LEFT(@domain, LEN(@domain) - LEN(@ext) - 1)
        END
        ELSE BEGIN
            SET @ext = @domain
            SET @domain = ''
        END
    END
    INSERT INTO @splitted ([Email], [Name], [DomainName], [Extension])
    VALUES (@email, @name, @domain, @ext)
    RETURN
END

Calling the function - for example:

DECLARE @@samples TABLE([mailid] NVARCHAR(255))

INSERT INTO @@samples ([mailid]) VALUES ('[email protected]')
INSERT INTO @@samples ([mailid]) VALUES ('[email protected]')
INSERT INTO @@samples ([mailid]) VALUES ('[email protected]')
INSERT INTO @@samples ([mailid]) VALUES ('[email protected]')
INSERT INTO @@samples ([mailid]) VALUES ('is@sane.')
INSERT INTO @@samples ([mailid]) VALUES ('is@sane')
INSERT INTO @@samples ([mailid]) VALUES ('[email protected]')
INSERT INTO @@samples ([mailid]) VALUES (NULL)
INSERT INTO @@samples ([mailid]) VALUES ('')
INSERT INTO @@samples ([mailid]) VALUES ('invalid')
INSERT INTO @@samples ([mailid]) VALUES ('@@@@@@@@@...')

SELECT e.*
FROM @@samples AS s
CROSS APPLY dbo.SplitEmail(s.mailid) AS e

will return this result:

Email                              Name                    DomainName        Extension
---------------------------------  ----------------------  ----------------  ------------
[email protected]      venkattaramanan1985     gmail             com
[email protected]                 madanraj                gmail             com
[email protected]             madanraj                sub.gmail         com
[email protected]                            sm                      al                l
is@sane.                           is                                        sane
is@sane                            is                                        sane
[email protected]                  rubbish                 h.h.h.h           h
NULL                                                                         

invalid                            invalid                                   
@@@@@@@@@...                       @@@@@@@@                ..