Mail data:
Mailid
-------------------------------
venkattaramanan1985@gmail.com
madanraj@gmail.com
Expected output
Name DomainName Extension
------------------- ---------- ---------
venkattaramanan1985 gmail com
madanraj gmail com
It has a simple one line solution, Assume email address is admin@system.org, 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.
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 ('venkattaramanan1985@gmail.com')
INSERT INTO @@samples ([mailid]) VALUES ('madanraj@gmail.com')
INSERT INTO @@samples ([mailid]) VALUES ('madanraj@sub.gmail.com')
INSERT INTO @@samples ([mailid]) VALUES ('sm@al.l')
INSERT INTO @@samples ([mailid]) VALUES ('is@sane.')
INSERT INTO @@samples ([mailid]) VALUES ('is@sane')
INSERT INTO @@samples ([mailid]) VALUES ('rubbish@h.h.h.h.h')
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
--------------------------------- ---------------------- ---------------- ------------
venkattaramanan1985@gmail.com venkattaramanan1985 gmail com
madanraj@gmail.com madanraj gmail com
madanraj@sub.gmail.com madanraj sub.gmail com
sm@al.l sm al l
is@sane. is sane
is@sane is sane
rubbish@h.h.h.h.h rubbish h.h.h.h h
NULL
invalid invalid
@@@@@@@@@... @@@@@@@@ ..
declare @var varchar(50) ='Abc.xyz@rediffmail.com';
select
SUBSTRING(@var,0,charindex('@',@var)) as UserName
,
Substring(@var,charindex('@',@var)+1,charindex('.',@var,charindex('@',@var,0))-(charindex('@',@var)+1)) as DomainName
,
substring(@var,charindex('.',@var,charindex('@',@var))+1,len(@var)) as Ext
I see the OP tagged the request as sql-server. I found this looking for mysql, go figure. So here is a mysql answer.
SUBSTRING_INDEX('john.doe@simon.says.com', '@', 1) AS Name,
SUBSTRING_INDEX(SUBSTRING_INDEX('john.doe@simon.says.com', '@', -1), '.', ROUND((LENGTH(SUBSTRING_INDEX('john.doe@simon.says.com', '@', -1)) - LENGTH(REPLACE(SUBSTRING_INDEX('john.doe@simon.says.com', '@', -1), '.', ''))) / LENGTH('.'))) AS DomainName,
SUBSTRING_INDEX('john.doe@simon.says.com', '.', -1) AS Extension,
The net result will return:
