Update table FirstName & LastName from Email addresses

  • I have a table with three fields, FirstName, LastName and EmailAddress. The EmailAddress field is already populated and follows a 100% consistent format e.g.: John.Dunne@MallWins.com, Lisa.Franklin@MallWins.com, Mary.Renaldo@MallWins.com, etc. I need to write an update query that will populate the FirstName and the LastName as one batch.

    I tried this to do just the FirstName and it gives me the FirstName, but errors with the UPDATE. I also can't seem to get the LastName extracted at all:

    update [a2hr].[dbo].[MallWinsRoster] set firstname = (select SUBSTRING([EmailAddress], 1,CHARINDEX('.', [EmailAddress])-1) from [a2hr].[dbo].[MallWinsRoster])
  • UPDATE ...

    SET FirstName = PARSENAME(LEFT(EmailAddress, CHARINDEX('@', EmailAddress) - 1), 1),
    LastName = PARSENAME(LEFT(EmailAddress, CHARINDEX('@', EmailAddress) - 1), 2)

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • That works and teaches me the PARSENAME function better which I'm still trying to understand, but this is most helpful in getting me started to understanding it!

  • I have a different set of data on which I want to use PARSENAME:

    Jamie Pourhussin (WTA) -692

    Brenda Johnson -11337

    Delia Rosenstiel -11256

    Rodderick Douglas LD051121 -13224

    Tynesha Townsend -14014

    Mikala Fenner -12881

    I can get the First name and the number extracted with the below but not the last name:

    SELECT ParseName(replace([agent name], ' ', '.'), 3) FROM [a2hr].[dbo].[stgOneConvoHrs] as Fname

    SELECT ParseName(replace([agent name], ' ', '.'), 2) FROM [a2hr].[dbo].[stgOneConvoHrs] as Lname

    SELECT ParseName(replace([agent name], ' -', '.'), 1) FROM [a2hr].[dbo].[stgOneConvoHrs] as EmpNum

  • Do/can your last names include any hyphenated names? Do you have any cases where you also have middle names (or two part last names with spaces)?

    If not, CHARINDEX & SUBSTRING can probably get that rather easily.

    What is "(WTA)" in the first example record? The other records seem to reliably separate names from EmpNum with a hyphen, but that field seems to complicate things. Are there any other gotchas in the data?

     

  • The last name is always the 2nd spot and the first is always the first sport and the employee number is always the number after the dash.

  • This less than eloquent way works until I get a name like (t) Robert Hester -15421 :

    Select Ltrim(SubString([agent name],1,Isnull(Nullif(CHARINDEX(' ',[agent name]),0),1000))) As FirstName,
    Ltrim(SUBSTRING([agent name],CharIndex(' ',[agent name]),
    Case When (CHARINDEX(' ',[agent name],CHARINDEX(' ',[agent name])+1)-CHARINDEX(' ',[agent name]))<=0 then 0
    else CHARINDEX(' ',[agent name],CHARINDEX(' ',[agent name])+1)-CHARINDEX(' ',[agent name]) end )) as LastName,
    ParseName(replace([agent name], ' -', '.'), 1) FROM [a2hr].[dbo].[stgOneConvoHrs] as num

    • This reply was modified 4 weeks ago by  DaveBriCam.
  • Let's fall back on the old reliable dbo.DelimitedSplit8K function, to avoid doing all the parsing ourselves.

    ;WITH sample_data AS (
    SELECT * FROM ( VALUES
    ('Jamie Pourhussin (WTA) -692'),
    ('Brenda Johnson -11337'),
    ('Delia Rosenstiel -11256'),
    ('Rodderick Douglas LD051121 -13224'),
    ('Tynesha Townsend -14014'),
    ('Mikala Fenner -12881'),
    ('(t) Robert Hester -15421'),
    ('-67890 Justincase Numbercomesfirst')
    ) AS data(name)
    )
    SELECT ca1.*
    FROM sample_data sd
    CROSS APPLY (
    SELECT
    MAX(CASE WHEN row_num = 1 THEN Item END) AS FirstName,
    MAX(CASE WHEN row_num = 2 THEN Item END) AS LastName,
    MAX(CASE WHEN Item LIKE '-%' THEN SUBSTRING(Item, 2, 10) END) AS num
    FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY ItemNumber + CASE WHEN Item LIKE '-%' THEN 20 ELSE 0 END) AS row_num
    FROM dbo.DelimitedSplit8K(name, ' ') ds
    WHERE Item <> '' AND Item NOT LIKE '(%'
    ) AS query1
    ) AS ca1

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • What is the code for the function DelimitedSplit8K?

  • Sorry.

    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE FUNCTION [dbo].[DelimitedSplit8K] (
    @pString varchar(8000),
    @pDelimiter char(1)
    )
    RETURNS TABLE WITH SCHEMABINDING
    AS
    /*SELECT * FROM dbo.DelimitedSplit8K('ab/c/def/ghijklm/no/prq/////st/u//', '/')*/
    RETURN
    /*Inline CTE-driven "tally table" produces values from 0 up to 10,000: enough to cover varchar(8000).*/
    WITH E1(N) AS (SELECT N FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS Ns(N)),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
    ctetally(N) AS (/* This provides the "zero base" and limits the number of rows right up front,
    for both a performance gain and prevention of accidental "overruns". */
    SELECT 0 UNION ALL
    SELECT TOP (DATALENGTH(ISNULL(@pString, 1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
    ), cteStart(N1) AS ( /* This returns N+1 (starting position of each "element" just once for each delimiter). */
    SELECT t.N+1
    FROM ctetally t
    WHERE (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0)
    )
    /* Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found. */
    SELECT ROW_NUMBER() OVER(ORDER BY s.N1) AS ItemNumber,
    SUBSTRING(@pString, s.N1, ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1), 0) - s.N1, 8000)) AS Item
    FROM cteStart s;
    /*end of function*/
    GO

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • DaveBriCam wrote:

    What is the code for the function DelimitedSplit8K?

    \

    If you'd like to learn more about it, there's a whole article on it.

    https://www.sqlservercentral.com/articles/tally-oh-an-improved-sql-8k-%e2%80%9ccsv-splitter%e2%80%9d-function

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply