CODEWITHSUNDEEP
sqlsql-serverfunction
Jennifer Jes
Jennifer Jes

Reputation: 35

Extracting username alone from an email address list

I need to extract only the username from a column holding list of email addresses separated by ;

[Column Name] Email Addresses

[email protected];[email protected];[email protected]

I want the output as user1;user2;user3

How do I get it in sql query?

Upvotes: 0

Views: 1197

Answers (3)

user7715598
user7715598

Reputation: 

DECLARE @TempData TABLE(YourEMail VARCHAR(100));
INSERT INTO @TempData VALUES
    ('[email protected];[email protected];[email protected]');

;WITH Expected
AS (
    SELECT SUBSTRING(YourEMail, 1, CHARINDEX('@', YourEMail) - 1) AS YourEMail
    FROM (
        SELECT Split.a.value('.', 'VARCHAR(100)') AS YourEMail
        FROM (
            SELECT 
                CAST('<S>' + REPLACE(YourEMail, ';', '</S><S>') + '</S>' AS XML) AS YourEMail
            FROM @TempData
            ) AS A
        CROSS APPLY YourEMail.nodes('/S') AS Split(a)
        ) dt
    )
SELECT DISTINCT STUFF((
            SELECT '; ' + YourEMail
            FROM Expected
            FOR XML PATH('')
            ), 1, 1, '') AS EmailDesiredOutPut

OutPut

  EmailDesiredOutPut
  --------------
  user1;  user2;  user3

Reurning Email_String AS Function

CREATE FUNCTION [dbo].[udf_ExtractName] (
@YourEMailIn nvarchar(MAX)
)
Returns varchar(max)
AS
Begin
DECLARE @YourEMailOut varchar(MAX)

DECLARE @TempData TABLE(YourEMail VARCHAR(100));
INSERT INTO @TempData(YourEMail) 

SELECT @YourEMailIn

;WITH Expected
AS (
    SELECT SUBSTRING(YourEMail, 1, CHARINDEX('@', YourEMail) - 1) AS YourEMail
    FROM (
        SELECT Split.a.value('.', 'VARCHAR(100)') AS YourEMail
        FROM (
            SELECT 
                CAST('<S>' + REPLACE(YourEMail, ';', '</S><S>') + '</S>' AS XML) AS YourEMail
            FROM @TempData
            ) AS A
        CROSS APPLY YourEMail.nodes('/S') AS Split(a)
        ) dt
    )
    , FinalResult
    AS (
SELECT  DISTINCT STUFF((
            SELECT '; ' + YourEMail
            FROM Expected
            FOR XML PATH('')
            ), 1, 1, '') AS EmailDesiredOutPut
       )
       SELECT @YourEMailOut=EmailDesiredOutPut From FinalResult

       RETURN @YourEMailOut

END
GO

--Call Function like below

 SELECT [dbo].[udf_ExtractName] ( '[email protected];[email protected];[email protected]') AS EmailDesiredOutPut

OutPut

EmailDesiredOutPut
-------------------
user1; user2; user3

Upvotes: 3

vinay koul
vinay koul

Reputation: 346

You can simply use this code..Just replace variable name with your column name..

Declare @email nvarchar(50)='[email protected]'
Select Substring(@email,0,CharIndex('@',@email))

Upvotes: 0

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

You should really never ever store more than one value in one cell!!!
This is breaking 1.NF already...
What you should do: Introduce a 1:n-related table to store each eMail-address with a a foreign key to its owner separately.

With SQL Server 2016 you could use STRING_SPLIT(), but with your version you must use one of the many workarounds. In the following code I use the XML trick first to splitt your string at each semicolon. The final SELECT combines LEFT() AND CHARINDEX to cut away the rest:

Try it like this

DECLARE @mockup TABLE(YourEMail VARCHAR(100));
INSERT INTO @mockup VALUES
    ('us&[email protected];[email protected];[email protected]');

SELECT LEFT(eMailAddress.value(N'text()[1]','nvarchar(max)'),CHARINDEX('@',eMailAddress.value(N'text()[1]','nvarchar(max)'))-1)
FROM @mockup AS m
CROSS APPLY(SELECT CAST('<x>' + REPLACE((SELECT REPLACE(m.YourEMail,';','$$SplitThisHere$$') AS [*] FOR XML PATH('')),'$$SplitThisHere$$','</x><x>') + '</x>' AS XML)) AS A(xmlSplit)
CROSS APPLY A.xmlSplit.nodes(N'/x[text()]') AS B(eMailAddress)

If you need the usernames re-concatenated again (think about this!), you can change the final query to this:

SELECT STUFF(
(
SELECT ';' + LEFT(eMailAddress.value(N'text()[1]','nvarchar(max)'),CHARINDEX('@',eMailAddress.value(N'text()[1]','nvarchar(max)'))-1)
FROM @mockup AS m
CROSS APPLY(SELECT CAST('<x>' + REPLACE((SELECT REPLACE(m.YourEMail,';','$$SplitThisHere$$') AS [*] FOR XML PATH('')),'$$SplitThisHere$$','</x><x>') + '</x>' AS XML)) AS A(xmlSplit)
CROSS APPLY A.xmlSplit.nodes(N'/x[text()]') AS B(eMailAddress)
FOR XML PATH(''),TYPE
).value('text()[1]','nvarchar(max)'),1,1,'')

Upvotes: 2

Related Questions