sql query for splitting of email

Question

input:-

column email:
kiran.paluri@live.com
shree.lavanya@gmail.com
krsna.kumar@yahoo.com

I want output as

firstname    lastname        domain
kiran          paluri          live
shree          lavanya         gmail  
krsna          kumar           yahoo

Possible duplicate of [T-SQL split string](https://stackoverflow.com/questions/10914576/t-sql-split-string) — devlin carnate, Jun 05 '18 at 17:10

score 0 · Answer 1 · answered Jun 05 '18 at 18:13

Below is a function that could do the parsing for you that takes into account if there is no period before the domain. Following that is an example of how to utilize it.

As Gordon alluded to, I'm not sure which db you're referencing so I've assumed SQL Server for the purpose of this answer.

CREATE FUNCTION dbo.splitEmail ( @email VARCHAR(MAX) )
RETURNS
 @ret TABLE ([FirstName] VARCHAR(MAX), [LastName] VARCHAR(MAX), [Domain] VARCHAR(MAX))
AS
BEGIN

    DECLARE @dotIndex INT, @atIndex INT
    SELECT @atIndex=CHARINDEX('@',@email)
    SELECT @dotIndex=CHARINDEX('.', LEFT(@email, @atIndex))

    DECLARE @first VARCHAR(MAX), @last VARCHAR(MAX), @domain VARCHAR(MAX)
    SELECT @first = CASE WHEN @dotIndex > 0 THEN SUBSTRING(@email, 0, @dotIndex) ELSE SUBSTRING(@email, 0,@atIndex) END
    SELECT @last = CASE WHEN @dotIndex > 0 THEN SUBSTRING(@email, @dotIndex + 1, @atIndex - @dotIndex - 1) ELSE NULL END
    SELECT @domain = SUBSTRING(@email, @atIndex + 1, LEN(@email) - @atIndex)

    INSERT INTO @ret([FirstName],[LastName],[Domain])
    VALUES (@first, @last, @domain)

    RETURN
END

Utilization of the function:

DECLARE @tbl TABLE (email VARCHAR(MAX))
INSERT INTO @tbl VALUES ('first.last@domain.com')
INSERT INTO @tbl VALUES ('john.smith@overthere.com')
INSERT INTO @tbl VALUES ('firstonly@contoso.com')

SELECT *
FROM @tbl AS t
    CROSS APPLY dbo.splitEmail(t.email)

sql query for splitting of email

1 Answers1