input:-
column email:
[email protected]
[email protected]
[email protected]
I want output as
firstname lastname domain
kiran paluri live
shree lavanya gmail
krsna kumar yahoo
Asked
Active
Viewed 628 times
0
Gordon Linoff
- 1,242,037
- 58
- 646
- 786
it's me
- 11
-
3Tag your question with the database you are using. – Gordon Linoff Jun 05 '18 at 17:04
-
Possible duplicate of [T-SQL split string](https://stackoverflow.com/questions/10914576/t-sql-split-string) – devlin carnate Jun 05 '18 at 17:10
1 Answers
0
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 ('[email protected]')
INSERT INTO @tbl VALUES ('[email protected]')
INSERT INTO @tbl VALUES ('[email protected]')
SELECT *
FROM @tbl AS t
CROSS APPLY dbo.splitEmail(t.email)
TomS
- 82
- 5